Time your Power Queries – #powerbi #powerquery

Sometimes your power queries can get quite heavy and you might need to optimize the steps in your query but how can you calculate the time it takes for your query to load.

Wouldn’t it be nice if you could have Power Query to do it for you

 Time your Power Queries – #powerbi #powerquery

Use Power Query to time Power Query

 Time your Power Queries – #powerbi #powerquery

Well – the Query dependencies window gave me the idea – what if I had a query with the start time of the refresh and then made the sales table dependent on that and then a duration table that where dependent on the sales table

Steps needed

First a query that calculates when the refresh is started

let

Source = DateTime.LocalNow()

in

Source

This will use the DateTime.LocalNow() to set the start time

 Time your Power Queries – #powerbi #powerquery

Now in this example I am loading a Excel file on my local harddrive on 22,3 mb with appx. 365.000 rows.

 Time your Power Queries – #powerbi #powerquery

After a navigation step and a promoted header step – I add a Custom Column where I refer to the Query “Start”

 Time your Power Queries – #powerbi #powerquery

This will add the start date to all rows in a separate column and will make the sales table dependent on the Query “Start”.

Next, we need to calculate the duration in a query that is dependent on the “Sales” table.

Step 1

 Time your Power Queries – #powerbi #powerquery

Create a calculation of Time now.

Step 2

 Time your Power Queries – #powerbi #powerquery

Convert it into a table

Step 3

To make it dependent on Sales I add a calculated column that retrieves the MIN value of the values “Start” in the table “Sales”

 Time your Power Queries – #powerbi #powerquery

Step 4

Rename the columns

Step 5

 Time your Power Queries – #powerbi #powerquery

Now we can calculate the duration in seconds by using the function Duration.Seconds() and subtracting [End] and [Start]

Step 6

And finally convert it to a decimal value

 Time your Power Queries – #powerbi #powerquery

The full query is now

let

Source = DateTime.LocalNow(),

#”Converted to Table” = #table(1, {{Source}}),

#”Added Custom1″ = Table.AddColumn(#”Converted to Table”, “Custom.1”, each List.Min(Sales[Start])),

#”Renamed Columns” = Table.RenameColumns(#”Added Custom1″,{{“Column1”, “End”}, {“Custom.1”, “Start”}}),

#”Added Custom2″ = Table.AddColumn(#”Renamed Columns”, “Query Duration”, each Duration.Seconds([End]-[Start])),

#”Changed Type” = Table.TransformColumnTypes(#”Added Custom2″,{{“Query Duration”, type number}})

in

#”Changed Type”

Then I disable the load of Sales table in order not to have Power Query read the file several times – (OBS be careful if you already have created measures on the table as the disable of load will remove these measures !!!!)

 Time your Power Queries – #powerbi #powerquery

To time the refresh I click the Refresh button

 Time your Power Queries – #powerbi #powerquery

And the card I have inserted in my report will show the number of seconds the query took.

Now let’s see what a conditional column cost

So, in the sales Query I add a conditional column that does evaluation on every row using the contains operator

 Time your Power Queries – #powerbi #powerquery

And click refresh again.

 Time your Power Queries – #powerbi #powerquery

Depending on your scenario you properly run the refresh several times in order to see the effect on your query.

Comments

Please let me know if you have comments or have solved how to time your power queries in another way.

Happy querying

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query