Over the weekend I found this nifty tool called Power Query Management Studio. Someone shared it on Twitter and you’ve probably seen the link to download the tool on technet. Basically this tool is a fancy Excel workbook that allows you to easily clean up your Power Query code and insert it back into your Excel workbook or Power BI semantic model. It’s pretty nifty and easy to use so I figured I’d give you a quick run down on using it to clean up my Power Query code in my Fantasy Football & NFL stats Power BI model, which you can download here.

To begin using the Power Query Management Studio, download it here.

I want to use this tool to clean up my Power Query code in my Power BI model, so the first thing I’ll do is open my Power BI model in Power BI Desktop. Next, we need to capture my Power Query queries so to do this I’ll click the smiley face icon at the very top of Power BI Desktop and click Send Frown.

A little Send Feedback dialogue box will pop up. Uncheck the Include Screenshot (since we really don’t care about that) and leave the Include Formulas box checked. This will allow us to see the Power Query queries. Click OK.

When you click OK, this will open up an email for you that will include your Power Query queries in the body of the email. The code is broken up by queries seperated by semicolons so you can easily see each query.

I copied everything below the line “section Section1;”. Once you’ve copied that code to your clipboard, open the Power Query Management Studio Excel workbook. Clear the sheet called CodePaste (but don’t delete the table) and paste your Power Query queries into the table like so. Then click the Refresh All button up top in the Data ribbon of Excel.

After a few moments, the Excel workbook will have completed its magic. There’s a few sheets in the workbook I’ll point out that you’ll find useful.

CommentTransfer: This sheet creates comments between each line of your code for your own documentation, explanation and excuses. You can then insert your own comments as necessary.

CleanedStepnames: This sheet auto cleans your code by removing unnecessary # symbols and spaces in step names.

Comments&Clean: This sheet is a combination of the previously mentioned sheets.

You can also paste a different version of your Power Query code into the sheet CodePasteVersion2 so compare version of your queries, which is very handy.

In my case, I added a test comment and copied the query for QBStats from the sheet Comments&Clean.

Once your code is copied, we just need to paste it into Power BI. Go to Power BI Desktop and click the Edit Queries button in the Home ribbon. Once in the Query Editor window, select the query you wish to modify, click Advanced Editor and paste your cleaned up query into the Advanced Editor window. At this point, you can only modify one query at a time so that’s the route you’ll have to take. In this Power BI model I only have a few queries so its not a big deal but if you had dozens of queries this could be tedious.

Make sure there are no syntax issues and click Done.

My Thoughts

All in all I think its a pretty neat tool for quickly adding comments to your Power Query queries and doing comparisons between versions of codes. I did run into a few quirks here and there with copying and pasting the code so just be careful with that, but for the most part I was able to easily add comments, review the queries and make changes.

Keep in mind that you can also use the Power Query Management Studio tool to modify Power Query queries included in traditional Excel workbooks.

Resources

Download the Power Query Management Studio Excel workbook here.
Download the help document for Power Query Management Studio.
Check out my Fantasy Football & NFL stats Power BI model

Feedback?

I thought this was pretty cool and useful. Did you find this as useful as I did? Let me know by leaving your comments below.