Visualize query dependencies in Power BI with Sankey diagram

Edit 7th August 2016: You’ll find an easier “OneInAll”-approach in the PowerBI forum here. Thanks to David Moss for reporting an issue with the solution below.

PBI-file with new query: NewAllInOneCode.zip ,

How to use:

Old post:

This week we got a new Sankey diagram. It has some advantages when visualizing dependencies between items/nodes, one being not to cut the labels. Time to share a tool that I use a lot when working with complex M-queries (in Power Query or Power BI). It shows how the queries are connected with each other:

Image1

Sankey diagram default

Left stand the queries who fetch the source data, parameters and/or user defined filters, then you see how they get transformed until to the right you see the queries that deliver your end-results. Especially when working with codes that has been written by someone else I find it very useful to get a quick oversight of what’s happening – as an excellent complement to the documentations 🙂

You can also move the nodes around to improve readability:

Image2

Sankey diagram with adjusted nodes

So far I couldn’t find a way to make this new layout survive a refresh or reopening of the file. If you know how this works, please share.

This technique is based on my Power Query Management Studio: An Excel-file that provides a couple of useful features helping you managing your Power Query work. In order to provide the format we need for the sankey diagram I’ve added another sheet (“FromTo”) which you can find in the file enclosed.

So in order to make this work you;

  • extract the code of the file you want to analyse by sending a frown. This will create an e-mail containing all the M-code. You shouldn’t send the e-mail but simply copy the code
  • open the Excel-file provided and paste the code into sheet “CodePaste”.
  • Data -> Refresh All will update the sheet “FromTo” that is the source for the pbix.

You could run the code in PBI as well and paste your code in the “EnterData” area. But if the code needs to be refreshed you would need to create a new query and change the the source of the existing ones. So when using it in a working environment, the Excel-source makes much more sense. So once you’ve downloaded and saved the files locally just adjust the sources to the xlsx-files in pbix to get it started.

PowerQueryManagementStudioV2_Final.xlsx
PowerQueryManagementStudioFinal.zip

Enjoy & stay queryious 🙂

Comments (11) Write a comment

  1. I followed above instructions and when i click RefreshAll in your excel file i get an error ….”The connection could not be refreshed…..” and then another error stating “The following data range failed to refresh ExterneDaten_1” …..any suggestions ???

    Reply

    • Hi David,
      thanks for reporting the issue.
      Although I’m not able to replicate it, I think an “All-In-One” solution in PBI would be preferable for it anyway.
      Have edited my post above.
      Imke

      Reply

  2. I haven’t heard of this problem before. But meanwhile you can very easily copy your code by just checking your query-names -> mouse-right-click -> copy. Then paste it whereever you need it: Into the query-editor of a new file will copy the queries incl. their dependencies and a paste into a text editor or simple table will paste the code of the queries. Pretty cool 🙂

    But unfortunately the code is formatted a bit differently this way, what makes the identification of the query-names more difficult. Please find a description of this alternative way here:
    http://community.powerbi.com/oxcrx34285/board/message?board.id=power-bi-designer&message.id=22922#M22922

    Reply

  3. Hi Imke, wanted to say thanks for providing this solution, took 5 mins to have it up and running and showing a great visual of the query flows. Appreciate you making this available. Cheers, Phil

    Reply

  4. Hi Imke, do you know if there is a way to have the Sankey nodes remain in place when manually moved around? I am finding that when I close the file and reopen, the nodes have shifted back to some default layout, even after being manually put in place. Cheers, Phil

    Reply

  5. I just discovered this and it looks awesome – thanks for sharing!

    Unfortunately I’ve found it breaks down when you have a Function generated by a Combine Files. The (non-editable) generated function ends with something like:

    in
    #”Changed Type”
    in
    Source

    This confuses the LetIn formula and results in a lot of the Queries being named “in”.

    Reply

    • Thanks Mike!
      have you tried the version from the forum whose link I posted at the top of the article?

      Reply

Leave a Reply