Query by Example to Extract YYYY-MM from a Date Column
I was answering a question for a student in one of my online training courses this week. The question was, “Do you know of a way in power query to efficiently extract YYYY-MM from a Date colu…
Power Query
Aggregate Text Values Using Group By in Power Query #12
Power Query can perform operations relevant to text when grouping data. Read on to learn how to group data and concatenate text values into a single cell.
Remove Duplicate Doesn't Work in Power Query for Power BI? Here is the Solution!
PowerQuery(M)agic: Conditional Joins using Table.SelectRows()
Business Problem: In a recent project with one of our clients, we were attempting to categorize website visit logs, based on the visitor’s landing page. The client had been maintaining a table of KeyPhrases that correspond with Categories. If the Visitor’s landing page contained the KeyPhrase, we wanted to match it
Handling Different Granularities In Power BI Using Slicer Groups
Reading Prathy Kamasani’s excellent blog post on Sync Slicers the other week I had a revelation: in the post Prathy shows how it is possible to sync selections between two slicers that are based on di…
Fill down hierarchies challenge in #PowerQuery
In today's blog post, I share the generic solutions for the Fill Down challenge that I posted here. As you recall, we had a table of backlog items with three levels of hierarchies (Scenario, Feature a…
Parameters and Functions in Power BI / Power Query–Custom Functions
What is a Custom Function in Power Query / Power BI, why we should use it whereas possible and what benefits it provides to our UX.
Create a Power Query custom data connector in 10 minutes
Getting set up When I heard about custom data connectors for Power Query, I had assumed there would be a lot of work involved. While there is definitely quite a bit of work in implementing advanced fe…
Thoughts On The New Power Query Source In SSIS
Yet more evidence that Power Query is taking over the world: there’s a new Power Query data source for SQL Server Integration Services in the latest version of SSDT. Here’s the blog post announcing it…
Camel Case Column Renaming – The new Way in #PowerBI #PowerQuery
A new splitter function has been discovered in Power Query (by Chris Webb and also others probably) which is not in the highlight notes of the February 2019 PBI release, but it is a very nice addit…
An Oldie but Goodie - PowerPivot In Excel: Existing Data Connections
With the Power BI craze that kicked into high gear in 2017, it’s less common to be working in PowerPivot in Excel. However, during one of my recent remote consulting calls, one of our many clients needed some help with a solution created in PowerPivot in Excel. And I often find if ONE person needs help or has a
Finding Power Query Query Execution Times In Power BI And Excel Using SQL Server Profiler
Working out how long a Power Query M query takes to run – the first step to troubleshooting refresh performance problems in Power BI or Excel – is something I have blogged about several times (see …
Splitting Text By Character Transition In Power BI And Excel Power Query
The February 2019 release of Power BI Desktop includes some new functionality not mentioned in the summary blog post, functionality that is already in the latest Excel 365 builds (thanks Ed Hansber…
Parameters and Functions in Power BI / Power Query - Main Concepts
Understanding the main principles of Parameters and Functions in Power Query and Power BI when it comes to Data Preparation and the M language.
Types in PowerQuery cannot be compared and a Type.AsText function
While searching for a compact formula to retrieve the textual representations of a type in Power Query, I learned that a type comparison in Power Query will return unreliable results, as "Type equivalence is not defined in M". So I continue using my old function, which I'm sharing here as well.
Excel.Workbook() And The delayTypes Option In Power Query/Power BI
A while ago I found myself wondering – as I often do about this kind of thing – about what the undocumented third parameter of the Excel.Workbook() M function (called delayTypes) actually does. I f…
Power Query – Combining Columns in M
In my Power BI courses I always recommend some books and sites that will help them learn DAX or M. Most of the time i'm presenting to new users to Power BI, and having to get them to move from the Exc…
Screenshot tutorial: Add a column with custom function code in Power Query – The BIccountant %
The following steps show how to create a new column in a table using existing custom function code. Applicable to Power BI and Power Query in Excel.
Bulk Find And Replace In Power Query
Learn how to do a bulk find and replace in power query based on a list of values.
Update your report based on a website
A quick hack today. Got this question from someone who needed to be able to update a report and show users that something had changed. This is easy when you...
Handling Unsuccessful HTTP Requests in Power Query M
Using error tables in Excel and Power BI to handle unsuccessful HTTP Requests.
The Fill Down Hierarchies Challenge
A weekend challenge I hope you are enjoying your second weekend of 2019. I was helping a friend recently figuring out the following data challenge: You have a table of Backlog items with three levels of hierarchies (Scenario, Feature and Backlog Item). In each row, the source data includes the unique ID of the lowest …
Writing data to GitHub using Power Query only
Using Power Query to write out data is usually a very bad idea. But I found an exeption where it made a lot of sense to me: Writing data to GitHub to feed..
Power BI Data Privacy Levels And Cloud /Web-Based Data Sources Or Dataflows
I have already blogged in great detail many times about Power BI/Power Query data privacy settings (see this series for example) but there’s always something new to learn. Recently I was asked a qu…
Power Query – Changing DateTime columns data types to Date without specifying the column names
This blog post I will demonstrate how to change a DateTime column data type to Date dynamically. You might be wondering what I mean by dynamic, this means that I will not specify any actual column …
Combine All Sheets in a Workbook with Power Query
There are quite a few different common business scenarios where you get data in the form of a file on a regular basis. The 3 common patterns I use most often are. Each day/month/year you get a new…
Merge Operations in Power BI / Power Query – Part 2: Right Outer Join
Facebook Twitter LinkedIn If you haven’t read Part 1 of this series, I highly recommend that you read that prior to this post. In that previous post, we went over the Left Outer Join and some basics on how Joins / Merge operations work inside Power Query / Power BI where the position of the …
ODBC Data Sources, The SqlCapabilities Option And Power Query/Power BI Data Refresh Performance
A few weeks ago I received a great tip from Simon Nuss about a Power Query feature relating to ODBC data sources that I hadn’t seen before (although it’s in Power BI Desktop and Excel 365, so it ca…
Export data from Power BI to csv using Python
Export data from Power BI to csv -files is best done using Python. Check this article for hows and whys:
Creating a Power Query function browser – #powerbi
One of the best functions in Power Query (M) is for sure the function =#shared that inside the Query editor lists all the functions in M and in your current pbix file. Picture 1 Result of =#shared But…