How List.Zip helps with Renaming Columns in Power Query

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.

Advertisements

6 thoughts on “How List.Zip helps with Renaming Columns in Power Query

  1. guavaq 2017-11-08 / 08:48

    Great blog post, and I have a question for you.

    Would you know how to put a space in for the column names, when each letter has a Capital.

    So for example if the column name is CasinoBrandBillingName, I would want the output to be “Casino Brand Billing Name”

    I have a lot of tables, with a lot of these columns, and it would be great to leverage what you explained above. I am just not sure how to find the location of each Capital Letter.

    • Ivan Bondarenko 2017-11-08 / 09:38

      Hi Gilbert,
      glad to see you here.
      Following function can split Capitalized Words / Camel Case

      let
      Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

      capitals = List.Buffer( {“A”..”Z”} ),
      fCheck = (char as text) => List.Contains( capitals, char),

      // Test area
      string = “FirstColumn”,
      step1 = Text.ToList( string ),
      step2 = List.Transform( step1, fCheck ),
      step3 = List.Transform( step1, each if fCheck(_) then ” ” & _ else _ ),
      step4 = Text.Trim( Text.Combine( step3 ) ),
      // end of Test area

      fTransformation = (string as text) =>
      let
      step1 = Text.ToList( string ),
      step2 = List.Transform( step1, each if fCheck(_) then ” ” & _ else _ ),
      result = Text.Trim( Text.Combine( step2 ) )
      in
      result,

      Custom1 = Table.TransformColumnNames( Source, fTransformation )
      in
      Custom1

  2. Bill Szysz 2017-11-08 / 16:07

    Different aproach:

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    HeadersChange = Table.TransformColumnNames(Source, each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {“A”..”Z”},2)) (_), ” “))
    in
    HeadersChange

    • Ivan Bondarenko 2017-11-09 / 02:23

      Just “Wow”, Bill you are M-aster, thanks for the great solution!

      • guavaq 2017-11-13 / 00:50

        Thanks to you both, that is a fantastic solution!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s