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:
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:
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:
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.
Enjoy & stay queryious 🙂