Hi Power BI Folks! Welcome to our 2nd post here at daxproservices.com.

Today, I wanted to share some thoughts about how to keep the house clean when naming the transformation steps of your Power Query queries.

This article is intended for Power BI developers with any level of expertise.

Scenario

You might have faced the scenario when you open a query in the Edit Queries interface of your Power BI file, and by just looking at the step names, it is Not clear on the logic it tries to perform. Let’s look at these query steps:

Without mentioning that the Query name is not meaningful, we would need to spend time trying to make sense of the steps, how it relates to the data, go over each one to see what it does, and hopefully figure out why it was done in the first place.

Goals

Now we want to rename these steps looking to:

• Improve readability
• Describe the operation type performed by the step
• Contextualize our data just by looking at the step name
• Maintain consistency all across

About the way Power Query names your steps

Power Query gives you a lovely UI to perform a plethora of data transformation operations, making it easy for anyone trying to get data into Power BI/Excel by not having to worry about writing any code.

Every time you interact with the UI and/or apply any operation from the Ribbon, Power Query creates a step that:

1. Refers to the output of the step above it
2. Writes a line of custom M code for you so you do not have to do it yourself. You can see this line of code in the Formula Bar
3. Is named after a generic operation name. Also, subsequent operations of the same type are number sequentially. For example: Changed Type, Changed Type1, Changed Type2…

Some generic operation names we find in Power Query are:

• Source
• Navigation
• Changed Type
• Added Conditional Column
• Added Custom
• Renamed columns
• Reordered columns
• Removed columns
• Replaced values
• … and many more!

The problem with these generic names is that despite of saying the operation performed, they don’t put your data in context so you can make sense of the process by just looking at them.

To rename a Query step, just do a right-click on the step and select Rename:

Follow these principles

Avoid long step names: specially if they overflow the default width on the interface. You should not need to expand the Query Settings pane to read the steps.

Use natural language when naming steps: avoid uncommon abbreviations that only you will understand, and write complete words in a natural way whenever possible to further improve readability.

DON’T leave the default name: beware that the UI names your steps with generic names. Take the time to properly name the steps meaningfully and clarity about what they do and what they act upon. Don’t fall for the I don’t have time excuse to name your steps because it will take your precious time later when you revisit your query. Leaving the default name is acceptable is your query is really short, it performs common steps, and if the steps are not repeated.

Be consistent with your our writing: name similar things in a similar fashion across all your queries, and all the datasets you manage. Encourage others in your team to do the same, and share thoughts about the best way to name things so everybody follows the same ideas. Promote department standards on Power BI naming conventions and make sure to include your Power Query steps and M code.

A naming convention for Query Steps

As a Best Practice, I suggest standardizing your step names with a 2-piece pattern:

[What it does + Context]

For “What it does”:

It is okay to stick to the default name verbs, such as “Added” when I add a Column, “Merged” and “Expanded” for merged queries, “Inserted” for manually added rows, “Appended” for combining queries (UNION), “Renamed” and so on.

For “Context”:

Specify the affected column(s) or table, in such a way that the query step is self-explanatory and makes sense to the reader.

Here are some examples of renames I like to do myself:

Going back to our first example, here’s what it looks like after some step renaming:

Just by looking at the renamed steps, now we can infer:

• The context is about Sales data
• The data comes from an Excel spreadsheet
• The steps “Promoted Headers”, “Changed Types”, and “Renamed Columns”, happen intuitively after importing data from a raw spreadsheet
• A field “City” is being Filled Down
• An Unpivot operation happens by City and Category
• There are two columns “Year” and “Gross Sales”

Which we can confirm with the data itself 🙂

Summary

• Always give your query steps a meaningful name! It will help you or anybody looking at your query infer what you are trying to accomplish.
• Examine your query to spot potentially unnecessary steps.
• Make a standard out of your own practice whenever possible, so everybody in the team is able to quickly read and understand the transformations

It is worth saying that I spend reasonable time reading articles from many of the wonderful authors writing about Power BI, but I think there’s more to write in this matter. If you believe there is material out there that we could reference, let us know! We are open to proactively contribute to the community knowledge about Power BI 🙂

I hope you liked this blog post! Feel free to share your thoughts down below, it will make us flatter. Keep in touch for more to come by following us on Twitter.