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
Follow Me
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.

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

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).
When Value 1 is missing, using MissingField.UseNull, will create a new column Amount 1 with null values.

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.
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!!
Pingback: The Ten Pitfalls of the Data Wrangler (aka the Wielder of M/Power Query) - PowerPivotPro
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"
Thank you for sharing, Colin.
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?
You may use an older version of Power Query.
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!
Pingback: How List.Zip helps with Renaming Columns in Power Query – Ivan Bond's blog
Can this solution be modified to replace down a column instead of across columns?
Can you please explain?