TIP: How to make your Power Query M code self-documenting

What were you thinking?

Do you remember exactly what you were thinking three months ago when you wrote that rather involved Power Query M statement? Me neither. So, why suffer when you can easily avoid the pain of code archaeology in Power BI? Piecing together a forgotten thought process around a particular problem can be a time consuming and frustrating task.

We’ve all seen this

You’ve seen Power Query M code like below. Chances are, you’ve created M code like below as well. You will be looking at this in a few months, thinking to yourself, removed what columns?!? and then I change the type. Awesome, what type and why did I do this?

Leverage these functions to hold your thoughts

I’ll take you through two features that you can use to document your thought process as part of your Power Query M code in Power BI. Your future self will love you for taking a few minutes to leverage these features to document your thought process. It also makes maintenance a breeze as it is rather easy to figure out where your data is retrieved.

Discipline is key…

Please note, this can feel tedious to do. It is quite easy to create many transformations in a short amount of time. If you take a few minutes to do this when  it is fresh in your mind, you’ll be better positioned if there are issues later or if you need to add functionality. The default names of the steps, like Renamed Columns 3 are not very helpful later.

How do I document my steps?

There are two techniques you can use to make your M code serve as documentation.

First, you can right click on a transformation step and select Rename to be whatever you wanted to document. For example, Renamed Columns above could be renamed to Renamed custUserID to User ID. This makes it very clear what was done.

Second, if you need more room to fully document a transformation step, right click on a transformation step and select Properties. The description field can be used for long form documentation.

Finished Product

We started with this list of transformations on a SharePoint list.

After taking the time to rename each step with what and why, it now looks like this.

I hope you find this post useful! If you have any questions, post them below in the comments.

UPDATE: This technique also works in Excel’s Power Query Editor as well.