Performance tip for aggregations after joins in Power Query and Power BI

This article was edited on 2nd Nov 2019. JoinKind.Local has been removed to avoid problems with merges on tables with primary keys:

In this article you’ll learn how to speed up the aggregation of joined/merged tables by orders of magnitude (I recorded up to 30 times faster execution times). This method works for merges where both table have multiple rows for each keys. If one of your tables has a primary key, the method Chris Webb describes here works just as good: Chris Webb’s article on how to improve performance on aggregations after joins using primary keys .

You can follow along the different methods in this file:  PerformanceAggregationsAfterMerges1_Upload.zip

Background

When you join a table to another table in Power Query, the UI gives you the option to either expand the columns (default) or aggregate the contents of the joint tables. That’s useful if multiple rows are returned for the rows of the table that has been joined to (left table):

Read more

Compare Power BI files with Power BI Comparer tool

Have you ever wanted to compare a version of a Power BI file with a previous one? … In the unlikely case that you haven’t yet, just wait until the auditors are in again – my new Power BI Comparer tool will save your day then 😉

Or maybe even before you uploade a new version of a report that has already been published to the service. How do you communicate the changes to your colleagues? Wouldn’t it be nice to have a tool that performs that comparison and documentation fully automagically?

Power BI Comparer

Fortunately my Power BI Comparer-tool makes it super easy to compare all properties of 2 Power BI files with each other: Just convert your pbix-files to pbit (as we need to access the data model properties as well) and drop the paths to these new files in my Excel-file like so:

Read more

Query folding on JoinKind.Inner gotcha for Power BI and Power Query

If you query databases who support query folding, you’re probably very aware of every step you take and check if folding happens with every new step like so:

“View Native Query” shows folding query that’s send back to the server (if not greyed out)

Folding will (usually) happen as long as “View Native Query” isn’t greyed out.

So when doing an inner join on tables whithin the same database, I was a bit surprised to see this greyed out actually. As according to the literature, it should fold.

But guess what? After I expanded a column from it, the folding was back again: Read more

How to get more out of your Graph API custom connector in Power BI

The Graph API can deliver a huge amount of interesting data from your Microsoft 365-universe, but the Graph API custom connector for Power BI is not able to retrieve everything from it in its current shape. So I’ve modified it a bit to squeeze out a bit more of its sweet juice.

Problem

When trying to get the details for planner tasks, the following error-message appears:

Error in Graph API custom connector when retrieving details from planner tasks

Solution

Read more

Easy unstacking of columns in Power BI and Power Query (and Excel)

The demand to unstacking a column into a table is not rare (see here for example: PowerBIForum  ) . Also if you copy a table from a post in the Power BI community forum  to the enter-data-section in Power BI, it will show up as such a one-column-table.

Task

Unstack column to table

 

Solution

Table.FromRows( List.Split( Table1[Column1], 3) )

..provided that your table is named “Table1”

How it works

Read more

RegEx in Power BI and Power Query in Excel with Java Script

From time to time Huang Caiguang sends me some geeky M-code that turns out to be very useful (like this for example). Today it’s utilizing java script code for RegEx (regular expressions): The Web.Page function can execute JS code and you can pass strings from you M-environment to it with simple string concatenation. So escape the js-code and use the ampersand (“&”) to reference the string. As a function it looks like so:

let
fx=(x,y)=>Web.Page(
“<script>
var x=”&x&”;var y=new RegExp(‘”&y&”‘,’g’);var b=x.match(y);document.write(b);
</script>”)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in fx(“””hello012中国1235″””,”\\d+”)

This code extracts all decimals (“\\d+”) from the string provided as the first argument and concatenates them with a comma. But just try for yourself by pasting the code into the advanced editor.

This doesn’t work in PowerBI service, so if you need it there, you can switch to an R-script that can be used with a personal gateway. But for Excel users, this is the way to go.

Please vote for a native implementation in M here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8892295-regular-expressions-support-in-power-query

Enjoy and stay queryious 😉