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 (10) 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. When I send a frown I only get about 1300 characters in the email so I only get the first few queries – I tried two different email programs and the results were identical – I am using Excel 2010 and the latest powerquery. Any suggestions of getting beyond the 1300 chars, or another way of extracting all the M source so I can check for dependencies

    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

Leave a Reply