When returning to some of my first queries built in Power Query, I always get a bit sad.
My old queries contain some unnecessary extra steps that makes it harder to evaluate what is happening in the query and in most cases I haven’t even bothered to rename the steps which makes the queries very hard to read for me and for others.
So, in order to improve here are some tips that can make mine and your queries shorter and easier to read.
Always Rename your Steps
Always spend time to rename your steps and avoid spaces in the step name.
Makes it easier to read and is documentation.
Especially important because the step name does not necessarily describe what is happening.
In this example I used the Remove Other columns function, and the step name refers to that function
– but what is actually happening is I am selecting specific columns – notice the formula bar – Table.SelectColumns
So, rename the step.
Check if you can avoid a column rename step
Using the interface in the Query Editor makes it very easy to get the “Renamed Columns” step but it can sometimes be avoided – For instance expanding a record column – in this example the DimGeography Column
– in this example the DimGeography Column and then double clicking the headers returned will lead into 2 steps but notice the last argument in the formula bar.
That is a list of column names that will be given and by modifying that we can do the expansion and renaming in one step.
Avoid the empty filter steps ie to not filter by (Select All)
When testing my queries, I sometimes filter the result to check the data
And then to clear it I sometimes use the (Select All) to remove the filter
This however leaves a filter step in your query where the Table.SelectRows function just is “each true”.
If you use the clear filter instead
The Filter step will be deleted
Limit the number of Rename Columns
Doing column renaming during the query steps can lead to several column renaming steps.
Try to do only 1 renaming step.
Limit the number of steps with Remove columns
Try to consolidate the column selection or deletion of columns in one step.
And remember the optional parameter in the Table.SelectColumns and Table.RemoveColumns where you can specify what should happen if a field in the column list is missing.
Don’t reorder your columns
You can change the column order in the Query editor but there is no need to do it
The columns will appear in alphabetical order in your data model anyway.
If you have a large table and can’t find the column use the Go to Column to locate it
A good bad example
Yes, this is one of my queries
This is the query after a clean-up – it does exactly the same but in fewer step and with a proper explanation in the step name.
Do you have any tips ?
This was a few tips to limit the number of steps in your query.
Let me know if you have any in the comments.