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.
Thanks for this, Very useful and something I’ll look to adopt.
Thank you for the tips.
I put a “prefix” in front of every step to group things together. For instance, if cleaning up the “product” column for a sales file import,
1) put the prefix “product” in from of every step related to this process
2) remove spaces in the step name; makes it easier to read the “M” code in Advanced Editor (removes the # symbols)
3) shorten the step names to improve readability
This
(a) creates a nicer visual story
(b) serves as documentation of my thought process
(c) makes it simpler to reuse the M code in other files where I am repeating the same type of cleanup.
Before:
Changed Type
Added Custom
Removed Columns
Renamed Columns
After #1:
product_ChangedType
product_AddedCustom
product_RemovedColumns
product_RenamedColumns
After #2:
product_Changed
product_Added
product_Removed
product_Renamed
Thanks for sharing. I never noticed this annoying # were related to spaces in the step names. Makes me wonder what the function of the # really is, and why a simple Double Quotes wouldn’t have sufficed.
Excellent, particularly like the right click rename
Another one would be to always rename a step that has a # placed on the front of it
I see creating queries as very similar to turning macro recorder on
It’s then down to you at the end to ‘clean’ them up so they are more structured and transparent. This is going to need to be an ongoing discipline as it’s easy to fire and forget
I wondered if you had noticed an oddity when importing from csvtext files when it produces the delimiter step. Should your csv change you need to manually delete the delimiter step and redo it. The delimiter step doesn’t appear to be dynamic
I’m doing a lot of exporting from data model as text files then reimporting using power query and noticed it
I don’t use a lot of CSV imports but have heard of others complaining. I have noticed in recent releases that there seems to be more “automatic” steps on import in general. I have found you can just delete these automatic steps and redo them if you don’t like what they do. I had a case where I imported some data via a Function. The initial connection converted a column to Whole Number without my knowledge. Then 4 weeks with a full set of data, there were some text rows turning up. This of course threw errors but it took me 30 mins to find what was wrong.
I think these “helpful” automatic steps are not always helpful.