Recently I’ve been working with the Power Query team to come up with some ways to help developers understand the data returned by the new Power Query diagnostics functionality. In this, the first of two posts, I’m going to share a Power Query function that reformats diagnostics data in a way that makes it easy to visualise using the Power BI Decomposition Tree visual.
Here’s the code for the function:
Here’s how to use it:
- Copy the code above into a new blank query in the Power Query Editor window, and give the query a meaningful name like DiagnosticsForDecompTree
- Pass in the name of a query created by using the Query Diagnostics functionality (you’ll be able to choose any query in your pbix file using the dropdown box) and invoke the function
- Load the output of the new query that gets created by invoking the function into your Power BI dataset
- Add a new Decomposition Tree visual to a report page
- Drag the Exclusive Duration column into the Analyze well of the visual
- Drag all the columns whose names start with “Level” (ie “Level.1”, “Level.2” and so on) into the Explain By well of the visual
- Add a filter or slicer on the Query column to filter the data shown in the visual down to a single query evaluation.
The output will look something like this:
What does this function do? The original output of a diagnostics query has one row for each event; each event may have one or more child event, and the Path column shows the details of this parent/child structure. The function takes this structure and expands it out to multiple columns, so for each event you see all of its ancestor events in a separate column on the same row. Since each event’s duration only gives you the time taken for it to run and doesn’t include the time taken its child events, when you break down an event in the Decomposition Tree you’ll see some child events with the name (Blank) – these give you the duration of just the parent event and make sure everything adds up properly. This function should work for both the detailed diagnostics output as well as the summary output.
You can download a sample pbix file showing an example of how to use it here.
This is only a first draft of the function, so I’d really appreciate it if you could try this with your own diagnostics data and check if it works properly and also if it produces useful output. Please leave your feedback in a comment either here or on the Github Gist. There are almost certainly going to be bugs!
Of course the real question is is how you interpret what you see in the Decomposition Tree and how you can use it to improve the performance of your queries. That, my friends, is a journey we’re on together, but I think that being able to visualise diagnostics data easily is an important first step to being able to understand what’s going on inside the Power Query engine when a query is evaluated.
[Thanks to Jorge Gomez Basanta, Colin Popell and Matt Masson for their help with this code]
Great stuff. Do you think the decomposition tree will be supporting SSAS MD in the future?
I don’t know, sorry.
Is it just me? Or DAX is simpler than M?
It’s just you 🙂
And Italians 😉
Thanks for sharing, Chris.
I tried and got below error at the end of invoking one of my query. Do you see a fix?
An error occurred in the ‘’ query. Expression.Error: The column ‘Id’ of the table wasn’t found.
Details:
Id
Are you invoking the function on a query that was created automatically by the diagnostics feature, and which contains diagnostics data?
The best way to make this effective is to put the timing in brackets next to name of each step in the applied steps pane
E.g
Source (2ms)
mData (1ms)
mRemOthCols (1 ms)
mRemDups ( 10 ms)
Miguel Llopis has promised to look into it during the next wave of improvements 🙂
Kindly remind him
It’s a nice idea, but because of the way Power Query works (eg query folding) I don’t think it would be possible to return timings for each step in a query.
So @Chris_Webb, you’re not so much a BI Developer as you are all things SQL Server? Is that how I should preface my replies going forward? “Chris, this is Chad Caulkins we talked sometime ago – if you didn’t know then we’ve got some serious catching up to do, mate!”
Cheers,
Chad Kolkin aka CCaulkins or ???
Is it just me, or does MDX seem way so similar to some of the Spotfire code, Cognos code, code that I’ve seen over the years? Even SAS’ attempt at creating dashboards much improved in 9.2 on
Solutions to the same problem will always look similar, I guess.
Thanks, Chris – if you’re ever out in the Baltimore/Washington, DC area for a few maybe we can get together –
I’m trying your code, but I get an error “invalid identifier” on PosList = {0..LevelNumber-1}. Do you have a solution?
This looks interesting, but the M function doesn’t seem to scale very well. My Diagnostics_Detailed file for one query is 27MB. The query in the sample pbix file that calls the function has been churning away on that for a few hours now.
I wonder if a solution using the DAX PATH functions might be more efficient. Or the raw data into a Sankey diagram (CV). Or something in python.