This is my second post about List.Zip. First one was about general usage of List.Zip, where I touched question of transforming column types in Power Query.

Another scenario where List.Zip can be used – renaming columns in Power Query.

When you rename columns manually, auto-generated function looks like

Table.RenameColumns( Source, 
     { {"Column1", "Col 1"}, 
     {"Column2", "Col 2"}, 
     {"Column3", "Col 3"}} )

As well as Table.TransformColumnTypes, it requires list of pairs {“Old Name” , “New Name” } for its second argument.

List.Zip helps to create list of pairs:

Table.RenameColumns( Source, List.Zip( { Table.ColumnNames( Source ), 
    { "Col 1", "Col 2", "Col 3" } } ) )

Result:

Dynamic column names

When we have, so called, “RenamingTable”, which contains two columns, first – with old name, second – with new name, we can use following pattern

Table.RenameColumns( TargetTable, 
    Table.ToColumns( Table.Transpose( RenamingTable ) ), 
    MissingField.Ignore )

You can read more detailed explanation in one of my previous posts.

Using List.Zip, we don’t need RenamingTable, as we can generate new names on the fly by using following pattern

NamesBuffered = List.Buffer( Table.ColumnNames( Source ) ),
Result = Table.RenameColumns( Source,
  List.Zip( { NamesBuffered, 
     List.Transform( NamesBuffered, YOUR_FUNCTION ) } ) )

Where YOUR_FUNCTION can be a function with any logic you want.

1. You can add dynamic suffix in column name, for example

NamesBuffered = List.Buffer( Table.ColumnNames( Source ) ),
NewNames = List.Transform( NamesBuffered , 
    each "Col " &
     Text.From( 1 + List.PositionOf( NamesBuffered, _ ) ) 
 ), 
Result = Table.RenameColumns( Source,
    List.Zip( { NamesBuffered, NewNames } ) )

I separated step with NewNames to make it easier to debug.

As YOUR_FUNCTION will work with names, potentially, multiple times, I suggest to buffer list with initial names using List.Buffer to prevent possible repeated queries to initial source.

2. You can replace spaces in column names

NamesBuffered = List.Buffer( Table.ColumnNames( Source ) ),
NewNames = List.Transform( NamesBuffered, each Text.Replace( _, " ", "_" ) ),
 // or
NewNames2 = List.ReplaceValue( NamesBuffered, " ", "_", Replacer.ReplaceText ),
Result = Table.RenameColumns( Source,
     List.Zip( { NamesBuffered, NewNames } ) )

If you are looking for the solution for renaming First N / Last N columns, I recommend you to read great post in Gil Raviv’s blog, and see last use case in this post.

Having this pattern you can easily adapt it to replace dots or any other characters in column names.

Can be even better? Yes!

As for “renaming” or “changing column names” in general, we have another function – Table.TransformColumnNames. Pattern from last section can be even shorter

    Result = Table.TransformColumnNames( Source, YOUR_FUNCTION ) 

For details you may read Imke’s post.

Use case – Trim Column Names in Power Query

Import from txt / CSV may lead to extra spaces in column names.

Method 1

    Result = Table.TransformColumnNames( Source, Text.Trim ) 

Text.Trim in Power Query removes only leading and ending spaces

You may still notice double space between “Hello” and “World”.

Method 2

Using PowerTrim (like TRIM in Excel). But you will have to define a function before you can use it.

If you need this function only in one query, you may include it as part of that query code (in Advanced Editor):

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 PowerTrim = (text as text, optional char_to_trim as text) =>
     let
         char = if char_to_trim = null then " " else char_to_trim,
         split = Text.Split(text, char),
         removeblanks = List.Select(split, each _ <> "")
     in Text.Combine(removeblanks, char),
 Result = Table.TransformColumnNames( Source, PowerTrim )
in
 Result

No double space between “Hello” and “World” now.

Use case – Rename Pivot Table Columns

Imagine situation when in Excel or from another source you get following table

Where last two columns are always “Prior Year” and “Current Year”. It means that extraction in next year will show you

Same position of columns – but new names.

If you want to add Growth calculation in Power Query you have to reference to these two columns. And as Power Query works with column names, it is a good idea to firstly rename them to “PY” and “CY”, otherwise you have to change formula each year.

There can be multiple tricky ways to do this.

My approach:

let
 Source = Excel.CurrentWorkbook(){[Name="SalesRange"]}[Content],
 #"Removed Top Rows" = Table.Skip(Source,1),
 #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
 buffer = #"Promoted Headers",
 NamesBuffered = List.Buffer( Table.ColumnNames( buffer ) ),
 Renamed = Table.RenameColumns( buffer,
     List.Zip( { List.LastN( NamesBuffered, 2 ), {"PY", "CY"} } ) ),
 AddedGrowth = Table.AddColumn( Renamed, "Growth", 
    each if [PY]<>0 then [CY]/[PY]-1 else null )
in
 AddedGrowth

If you want to restore initial names later on

= Table.RenameColumns( AddedGrowth,
       List.Zip( { List.FirstN( Table.ColumnNames( AddedGrowth ), List.Count( NamesBuffered ) ), 
                                  NamesBuffered } ) )

Potentially, we could add more than one column, and we don’t need to rename them. Therefore, we have to exclude them by taking FirstN columns, where N is number of columns in NamesBuffered (initially buffered list of column names).

Moreover, if we’d use straightaway

    List.Zip( { Table.ColumnNames( AddedGrowth ), NamesBuffered } )

Table.RenameColumns would give us an error because List.Zip will return null for all new columns, as they don’t exist in NamesBuffered.

Workbook with described examples can be downloaded from this link.

In the next post, I will show how to use Table.TransformColumns in combination with List.Zip.