Column Renaming and List.Zip To The Rescue – Pitfall #6 in #PowerBI #PowerQuery

Today, we will learn how to avoid the 6th pitfall as part of the Power BI / Power Query Pitfalls series. The sixth pitfall is quite tricky, and is one of the most common pitfalls you always encounter.

Column Renaming


Renaming of columns is a necessity in any reporting solution. Whether you tap into a centralize data source, or an ad-hoc text file, often times the original column names in the external data source are not the desirable names you would want to expose to your report consumers (who will most likely perceive these column names as utter gibberish).

Like the previous two pitfalls of reordering and removal of columns, unthoughtful renaming of columns will expose you to refresh failures, and will leave a flaw in your query. In this blog post, we will quickly review the relevant options to avoid the pitfall, and introduce you with a magic formula that will Zip your troubles away.

Let’s demonstrate the pitfall and its solutions by walking you through a simple scenario. Imagine, we have the following table, and want to rename the last three columns to Amount 1, Amount 2 and Amount 3.

Renaming Columns in Power BI Query Editor - Pitfall #6
Let’s see what happens when we rename the columns Value 1, Value 2 and Value 3 to Amount 1, Amount 2 and Amount3

When we perform the rename, we can see the following code in the formula bar (Remember Pitfall #1, always have your formula bar open, and watch out for references to column names, or other static short-time elements like dates and sporadic values).

Renaming Columns in Power BI Query Editor - Pitfall #6
Renaming a column, leaves a trace of the original name in the formula

In case Value 1, Value 2 or Value 3 will be missing from the external data source, our query will fail. Note: If you want to see a real example for a bad rename, check out the second scenario in my blog post on PowerPivotPro. In that post, I had all the column names in the first row, but the last column included a value (e.g. London), and not a column name (e.g. City). When I promoted the first row as headers, and renamed the column (From London to City), I exposed my query to a highly-probable refresh failure.

MissingField.Ignore & MissingField.UseNull

If you followed Pitfall #4 and #5, by now you know this drill. You can use MissingField.Ignore or MissingField.UseNull as a third and optional argument in Table.RenameColumns.

In our example, if Value 1 is replaced with the column Value – Using MissingField.Ignore will keep the column Value untouched, and will rename the columns Value 2 and Value 3 to Amount 2 and Amount 3 (respectively).

You can use MissingField.Ignore as a 3rd argument to avoid the 6th pitfall

When Value 1 is missing, using MissingField.UseNull, will create a new column Amount 1 with null values.

You can use MissingField.UseNull as a 3rd argument to avoid the 6th pitfall
You can use MissingField.UseNull as a 3rd argument to avoid the 6th pitfall

The best solution – List.Zip

In the last section of Pitfall #5, we used a formula that allowed us to refer to columns by their position in the table, instead of their names. In many cases, this approach would be your best bet in delivering robust queries.

In this section, we will show you how create the magic formula that will rename columns by their position.

So, instead of using the code below to rename the last three columns from Value 1, Value 2 & Value 3 to Amount 1, Amount 2 & Amount 3

let
    Source = Source,
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Value 1", "Amount 1"}, {"Value 2", "Amount 2"}, {"Value 3", "Amount 3"}})
in
    #"Renamed Columns"

We will use List.LastN on Table.ColumnNames to get the list of the last three column names

{“Value 1”, “Value 2”, “Value 3”}.

ColumnNamesOld = List.LastN(Table.ColumnNames(Source), 3),

Next, we will assign the new column names to a new list

{“Amount 1”, “Amount 2”, “Amount 3”}

ColumnNamesNew = {"Amount 1", "Amount 2", "Amount 3"},

Finally, we will create a nested list of pairs from both lists, which is needed as the second argument of Table.RenameColumns

{{“Value 1”, “Amount 1”}, {“Value 2”, “Amount 2”}, {“Value 3”, “Amount 3”}}

To create the nested list of old/new pairs, we will use List.Zip (read more about List.Zip here)

ZippedList = List.Zip( { ColumnNamesOld, ColumnNamesNew } ),

And finally, here is the entire M expression you need to rename the last three columns to Amount 1, Amount 2 and Amount 3.

let
    Source = Source,
    ColumnNamesOld = List.LastN(Table.ColumnNames(Source), 3),
    ColumnNamesNew = {"Amount 1", "Amount 2", "Amount 3"},
    ZippedList = List.Zip( { ColumnNamesOld, ColumnNamesNew } ),
    #"Renamed Columns" = Table.RenameColumns(Source, ZippedList)
in
    #"Renamed Columns"

And here is a generic function that receives the new names as a list, and the indices as a second list, and perform the column renaming.

(Source as table, ColumnNamesNew as list, Indices as list) =>
let
    ColumnNamesOld = List.Transform( Indices, each Table.ColumnNames(Source){_} ), 
    ZippedList = List.Zip( { ColumnNamesOld, ColumnNamesNew } ),
    #"Renamed Columns" = Table.RenameColumns( Source, ZippedList )
in
    #"Renamed Columns"

If you call the function above FnRenameColumnsByIndices, here is how you can use it to rename the last three columns in our example:

= FnRenameColumnsByIndices( Source, {"Amount 1", "Amount 2", "Amount 3"}, { 2,3,4 } )

Now that we are better acquainted with column renaming, I hope you will “Zip” the column names to achieve robust queries.

Hope you enjoyed the sixth pitfall. Can you guess what is the seventh pitfall? Share in the comments below.

10 comments

  1. Konrad Schaefers Reply

    This is awesome! I have struggled with this problem for a while. When accessing web data the table that is returned usually depends on the language of the user’s system. I am using English settings, but if my colleague has german language settings most of the queryies won’t work, because the column headers are different on his system. This techinque allows me to replace the coumn headers with a common standard.

    thanks for the post!!

  2. Pingback: The Ten Pitfalls of the Data Wrangler (aka the Wielder of M/Power Query) - PowerPivotPro

  3. Colin Reply

    thanks for this great tip. I made a modification that allows you to skip the Indices if you want to just rename the first however many columns you provide names for. Not sure if this is the cleanest way to do it but it works.

    (Source as table, ColumnNamesNew as list, optional Indices as nullable list) =>
    let
    //trying list.count should error on a null value so we can catch this and make the count 0
    IndexCount = try List.Count(Indices) otherwise 0,

    //if no indices are specified, replace Indices with first n columns, where n is a count of the items in the new names list
    Indices2= if IndexCount 0, each _ < List.Count(ColumnNamesNew), each _ + 1) else Indices,

    ColumnNamesOld = List.Transform( Indices2, each Table.ColumnNames(Source){_} ),
    ZippedList = List.Zip( { ColumnNamesOld, ColumnNamesNew } ),
    #"Renamed Columns" = Table.RenameColumns( Source, ZippedList )
    in
    #"Renamed Columns"

  4. Grace Reply

    Thanks Gil for this post. I’m trying to use List.Zip but am getting this error message “Expression.Error: The name ‘List.Zip’ wasn’t recognized. Make sure it’s spelled correctly.”.

    Any idea why?

  5. Grace Reply

    Thanks Gil, it’s a version issue after all. My Excel was 1609 when the latest available is 1702. Have found a fix for it and updated, thanks!

  6. Pingback: How List.Zip helps with Renaming Columns in Power Query – Ivan Bond's blog

  7. Curtis Reply

    Can this solution be modified to replace down a column instead of across columns?

Leave a Reply