Tidy Up Power Query

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).

image

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.

image

You will get a warning message. It is fine – just click Delete.

image

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.

image

This is much easier to read now.

image

Share?

Comments

  1. 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.

  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x