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

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.