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.
Stay queryious.
Erik Svensen - Blog about Power BI, Power Apps, Power Query 
Great post, I’d add limit the number of change data type steps. Try to apply a data type as part of another step, many of the functions include an option to apply a data type eg Number.Type or Text.Type so if you create a conditional column you can enter a comma at the end of the formula and enter the data type in the formula bar eg “,Number.Type)”.
Thx – Matt – And yes – a very good case to avoid extra steps as well !!
Here are my rules to follow.
1) Row Reduction (Table.SelectRows) and Column Reduction (Table.Selectcolumns) should happen as early as possible in the Query
2) Change Type should happen as late as possible – preferably the last step – unless a change type is needed to achieve a Transformation or Row reduction
3) Never Sort , Reorder Columns in PQ – best done on the Client side (Pivot, visual etc)
4) Rename the Navigation step to Navigation in the Advanced Editor
5) When connecting to Excel Tables change the 2nd parameter from Null to true -to avoid a promote header step
6) Never to Joins for Expanding Factables by bringing columns from Dimensions – Use Joins to achieve row reduction – So Doing a Join on a Emp ID to bring in a Department name is a Fact table from a HR Dim is a bad idea – but brining in a Status column to then filter for Active Employees and then remove the status column is a good use of a join
7) In the Navigation step – never use Source{[Item=”Data”,Kind=”Sheet”]}[Data] instead Source{[Name=”Data”]}[Data] is fine – Kind=”Sheets” ties you to Excel as a datasource and does not work for xlsb files
8) When connecting to a database steps that cannot be folded should happen as late as possible.