Visualising Power Query Diagnostics Data In A Power BI Decomposition Tree

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:

(Input as table)=>
Source = Table.Buffer(Input),
RemoveColumns = Table.SelectColumns(Source,{"Id", "Query", "Step", "Category", "Operation", "Exclusive Duration", "Path"}),
ReplaceNullsInPath = Table.ReplaceValue(RemoveColumns,null,"",Replacer.ReplaceValue,{"Path"}),
GetNodeOperation = (pId, pPath) as text =>
FilterRows = Table.SelectRows(ReplaceNullsInPath, each [Id]=pId and [Path]=pPath),
FirstRow = FilterRows{0},
Op = try FirstRow[Operation] otherwise "Trace Gaps"
CreatePath = Table.AddColumn(ReplaceNullsInPath, "Level", each
LevelNumber = List.Count(Text.Split([Path],"/")),
CurrentPath = [Path],
CurrentId = [Id],
PosList = {0..LevelNumber1},
GenerateText = List.Transform(PosList, each CurrentId & " " & Text.BeforeDelimiter(CurrentPath, "/", _) & " " & GetNodeOperation(CurrentId, Text.BeforeDelimiter(CurrentPath, "/", _))),
Output = Text.Combine(GenerateText, "|")
FindMaxLevels = Table.AddColumn(CreatePath, "NumberOfLevels", each 1+Text.Length( Text.Select([Level],{"|"}))),
MaxLevels = List.Max(FindMaxLevels[NumberOfLevels]),
SplitLevels = Table.SplitColumn(FindMaxLevels, "Level", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),MaxLevels),
RemoveMaxLevels = Table.RemoveColumns(SplitLevels,{"NumberOfLevels"}),
CalculateDuration = Table.TransformColumns(RemoveMaxLevels,{{"Exclusive Duration", Duration.TotalSeconds, type number}})

Here’s how to use it:

  1. Copy the code above into a new blank query in the Power Query Editor window, and give the query a meaningful name like DiagnosticsForDecompTree
  2. 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 functionInvokeFunction
  3. Load the output of the new query that gets created by invoking the function into your Power BI dataset
  4. Add a new Decomposition Tree visual to a report page
  5. Drag the Exclusive Duration column into the Analyze well of the visual
  6. Drag all the columns whose names start with “Level” (ie “Level.1”, “Level.2” and so on) into the Explain By well of the visualWell
  7. 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]

16 responses

  1. 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.

  2. 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


    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!”


        Chad Kolkin aka CCaulkins or ???

  3. Pingback: Data Model Miscellany | Professional Visual Analysis with Excel

  4. 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

  5. Pingback: Chris Webb's BI Blog: An M Function To Help You Explore Power Query Diagnostics Data Chris Webb's BI Blog

  6. Pingback: Visualising Power Query Diagnostics Data In A Power BI Decomposition Tree |

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: