Performance tip to speed up slow pivot operations in Power Query and Power BI

Pivot operations in are a very handy feature in  Power Query but they can slow down refresh performance. So with some bittersweet pleasure I can tell that I found a trick to speed them up. The sweetness comes from the fact that the performance improvement is very significant. But the bitterness comes from the fact that I could have used this for almost 4 years now, but was too blind to realize at the time when I first worked with the code.

Trick to speed up a slow pivot table

This might not work everywhere, but for my tests, it worked really well: Don’t use an aggregation function when you want fast pivoting:

slow pivot

Don’t aggregate when you want a fast pivot in Power Query

But if your data isn’t aggregated on the row- & column values already, you’ll get this error message:

Error when the values are not adequately aggregated

So to make this work, you have to aggregate the values on the axis’ values before.

Let’s walk through the steps:

Walkthrough

Read more

Perfect Analysis Services (SSAS) reports in Excel using Power Query

Excel-reports on SSAS cubes (multidimensional and tabular) can have some flaws that now can be overcome by using Power Query for sourcing your cubedata:

  • filter your cube by complete Excel-tables without loading them to the model/cube
  • Apply nice number and date formats to non-measure number and date fields in your row- or column section
  • create fast detailed reports (multiple attributes in your row sections, overcome the slow MDX that the pivots on cubes produce)

As with the recent Power Query update (26) you can now create your own MDX and DAX-statements for retrieving data from a cube, it is also possible to pass individual parameters from your Excel-sheet to the queries. This is a prerequisite for dynamically reducing the number of returned fields to the query, thereby allowing a decent performance of these reports.

So how about filtering the query by a table that sits in your local Excel file? Can we do an inner-join just like on the SQL-server-sourceRead more

How to pivot multiple measures and columns in Power Query and Power BI

The pivot-function in Power Query lets you pivot your data within the query already. This comes in handy quite often when you want to further work on those results within the query. There’s not much to parametrise in the function, so the question is how to handle it when you want more than one measure/value to be returned.
Let’s say you have a table like this:

Ausgangstabelle

And it shall look like this:

02_Ziel

Read more