I have been doing a lot of work with Power Query for clients in recent months. It is not always obvious exactly what you need to do when you start writing a Power Query. As a result I have found that the Power Query workbooks tend to “evolve” during the process, and they can sometimes be a bit messy in the end. This had me thinking about how to tidy things up a bit before finishing. There are 2 tips that I have.
1. Remove all but the last Reorder Step
In the example below, I have loaded the customer table from Adventure Works. During the process of creating the query I changed the order of the columns on a couple of occasions (shown below).
It is not immediately obvious, but you actually don’t need any Reordering step other than the last one. So the first tip is simply delete all but the last reordering step.
You will get a warning message. It is fine – just click Delete.
You can do this for any and all Reordered Columns in Power Query except for the last one and it will have no effect on the final result.
2. Rename Significant Steps
While it is not essential that you rename the steps, it can be very beneficial if you rename the “significant steps” in your query. This makes it easier to come back to the query later and work out what you have done. To rename a step, just right click and give it a new more meaningful name.
This is much easier to read now.