Posts in Category ‘Power Query’

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

Start is this table:

Start

The pivot shall bring the values from the “Column”-column into the column-area and sum the values from column AB like so:

Result

If I pivot without an aggregation like mentioned above I will get the dreaded error-message like above, because there are multiple rows for each Row- and Column-combination:

slow pivot power query performance

Values are not aggregated on the row- and column axis

The step to success is a grouping operation beforehand:

slow pivot power query performance

Group on all columns that shall define the row- & column values of the pivot

This returns a table with unique row- & column – combinations:

Aggregated table with just as many rows as the number of fields in the desired pivot table

9 rows for a desired 3×3-matrix looks just about right. So if I pivot here, there will be no further aggregation needed and the desired result will be shown.

Who found it?

Genius Bill Szysz used this method to speed up a slow pivot in his code to speed up matrix multiplication:

slow pivot power query performance

Code from Bill Szysz for a fast matrix multiplication (posted by DataChant)

This article is almost 4 years old, and I’ve even played around with the code at that time. Sight.. 4 years wasted time where I didn’t realize that the key for the performance improvement lied in a technique that would significantly improve the refresh speed of so many other applications as well…

Why does it work?

Here is my guess:

It looks as if the group operation creates some primary keys that create partitions for every row (or even every cell?) of the pivot table to be. I tested this guess by adding a primary key on those 2 columns (instead of grouping) and the refresh time sped up just like with the group operation. So if your data is already aggregated to the right level you can just add a key (or remove duplicates – as long as you don’t loose any rows), no need to do the group.

This means that the pivot operation doesn’t have to work on the full table, but just on the partitioned parts. (In this article I have described the performance improvements through partitions the first time).

But this also reminds me of the performance improvement for aggregations after joins, that I’ve blogged about here. Let’s see if there will be more use cases to be found.

Enjoy and stay queryious 😉

paging pagination Power Query

How not to miss the last page when paging with Power BI and Power Query

When you use an API with a paging mechanism (like the example from this blogpost), you’ll might work with a field that contains the address for the next page. You can use this to walk through the available chunks until you reach the last element. That last element in the pagination will not contain a next-field or that field will be null.

Paging in Power Query

In Power Query you can use the function List.Generate for it. According the latest function documentation it:

Generates a list of values given four functions that generate the initial value initial, test against a condition condition, and if successful select the result and generate the next value next.

So an intuitive implementation would look like so:

paging pagination Power Query

Initial code for paging: Will miss the last element

In the initial step (row 2) the API will be called and returns this record:

paging pagination Power Query

Examining the result of the first call

So for the upcoming iterations (next in row 4), a reference to the field next will be made and this URL will be called.

In the condition (row 3) I say that this process shall be repeated until the next-field of my previous result ([Result]) is empty.

However, this will only return 14 list with 20 elements each, missing the last element with 13 items to retrieve the full 293 items.

Let’s check it out:

Last Element (13 rows) is missing

Solution

Honestly, I still find it difficult to understand, why this last element is missing. But fortunately there is an easy solution:

paging pagination Power Query

Split into 2 steps and reference previous URL instead

The trick lies in the adjusted condition (row 4): Instead of checking if there is a next-field in the previous record, I check if the previous record had a URL to call. That basically reaches 1 level further back and will deliver the full results.

Alternative

Actually, you can also use some “brute force” using a try – otherwise – statement like so:

Simple alternative

But this will not deliver any items for debugging if something in the calls goes wrong. So I prefer not to use try statements for looping or pagination.

Enjoy and stay queryious 😉

Automatically detect and change the types of all columns at once in Power Query

Today I want to share quick tip on how to automatically detect and change all column types at once in Power Query.

Background

Very often, when you expand a column in Power Query that contains a table or some records, the expanded columns will lose their types (like Chris Webb has described here for example). Or you might just have accidently deleted a “Changed Type”-step.

change all column types at once

No types on columns

Did you know there is actually a superfast and easy way to do it?

  1. Click the mouse anywhere in the table
  2. Press Ctrl + a (check all)
change the types of all columns

Check the whole table with Ctrl + a

  1. Go to the Transform-tab ad choose: “Detect Data Type”
change all column types at once

Transform with 1 click

Voila: All your columns should have types on them.

They have been automatically been detected by checking the first 100 rows of your table. So if you know that you’re having columns with inconsistent values in them, make sure to check the automatically assigned values.

Enjoy & stay queryious 😉

Create a load history or stage in CDS instead of incremental load in Power BI

If you’ve been following my blog for a while, you might have noticed my interest in incremental load workarounds. It took some time before we saw the native functionality for it in Power BI and it was first released for premium workspaces only. Fortunately, we now have it for shared workspaces / pro licenses as well and it is a real live saver for scenarios where the refresh speed is an issue.

However, there is a second use case for incremental refresh scenarios that is not covered ideally with the current implementation. This is where the aim is to harvest and store data in Power BI that will become unavailable in their source in the future or one simply wants to create a track of changes in a data source. Chris Webb has beaten me to this article here and describes in great detail how that setup works. He also mentions that this is not a recommended setup, which I agree. Another disadvantage of that solution is that this harvested data is only available as a shared dataset instead of a “simple” table. This limits the use cases and might force you to set up these incremental refreshes in multiple datasets.

Read more

Power BI “Store datasets in enhanced metadata format” warning

This is just a quick heads up for the new preview feature “Store datasets in enhanced metadata format“. You should definitely think twice before turning this feature on:

Background

With the march release came function “Store datasets in enhanced metadata format”. With this feature turned on, Power BI data models will be stored in the same format than Analysis Services Tabular models. This means that they inherit the same amazing options, that this open-platform connectivity enables.

Limitations and their consequences

But with the current setup, you could end up with a non-working file which you would have to build up from scratch for many parts. So make sure to fully read the documentation . Now!

In there you find this warning:Store datasets in enhanced metadata format

Warning for the new enhanced metadata format Read more

Tips to download files from webpages in Power Query and Power BI

When downloading data from the web, it’s often best to grab the data from APIs that are designed for machine-to-machine communication than from the site that’s actually visible on the screen. Not only is the download usually faster, but you also often get more additional parameters that can be very useful. In this article I’m going to show you how to retrieve the relevant URLs for downloading files from webpages (without resorting to external tools like Fiddler) and how to tweak them to your needs.

Retrieving the URL to download files from webpages

Say I want to download historical stock prices from this webpage:

https://finance.yahoo.com/quote/AAPL/history?p=AAPL

The screen will show a link to a download: Read more