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.
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.
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
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
Just “Wow”, Bill you are M-aster, thanks for the great solution!
Thanks to you both, that is a fantastic solution!
Hello,
Thanks for resource and great blog!
Failed to rename columns when use Cyrillic. Same query works if new names are with non-Cyrillic characters. Wonder why.
Any idea?
Best regards
Todor Todorov
Hi Todor, thanks, appreciate your feedback.
I just tried sample file from the post, replaced headers with Cyrillic names and query still works fine for me.
Can you make a simple Excel file with the example that doesn’t work for you?
Thanks,
Ivan
Hi Ivan,
Thanks a lot for quick response!
Situations looks strange. Quite simple query:
1. Initial information is loading from dynamic named range (SeparationForMerging). Named range SeparationForMerging is defined with formula =INDEX(‘Files-Print-Packaging’!$K$7:$LC$301;1;1):INDEX(‘Files-Print-Packaging’!$K$7:$LC$301;MATCH(“TOTAL”;’Files-Print-Packaging’!$L$7:$L$301;0)+1;ROWS(FileList)+5)
2. Promotion of headers
3. Get names. Use parameter CountFiles which count rows with filled information in predefined area (FileList). CountFiles is parameter query, getting information from cell in sheet Files-Print-Packaging
4. Loading parameters in RenamingTable. RenamigTable is simple query with code:
let
Source = Excel.CurrentWorkbook(){[Name=”FileList”]}[Content],
#”Renamed Columns1″ = Table.RenameColumns(Source,{{“Column1”, “List”}})
in
#”Renamed Columns1″
5. FileList is dynamic named range with formula =INDEX(‘Files-Print-Packaging’!$F$8:$F$301;1;1):INDEX(‘Files-Print-Packaging’!$F$8:$F$301;COUNT(IF(‘Files-Print-Packaging’!$F$8:$F$301=””;””;1));1)
6. Renamig with information like this Text 1, Тext 2, etc. is possible and works. When type text in Cyrillic query generate error message with text: Expression.Error: The field ‘Плакат [Визия 1]’ already exists in the record.
Noticed that step-by-step executing initial query, that when text is Cyrillic then text is between “.
Tried to remove it with RemoveQuotes = List.ReplaceValue(ColumnNamesNew,””””,””,Replacer.ReplaceText), but query is still not working.
All query looks like this:
let
Source = Excel.CurrentWorkbook(){[Name=”SeparationForMerging”]}[Content],
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ColumnNamesOld = List.LastN(Table.ColumnNames(PromotedHeaders), CountFiles),
ColumnNamesNew = Table.ToList(RenamingTable),
RemoveQuotes = List.ReplaceValue(ColumnNamesNew,””””,””,Replacer.ReplaceText),
ZippedList = List.Zip( { ColumnNamesOld, RemoveQuotes } ),
#”RenamedColumns” = Table.RenameColumns( PromotedHeaders, ZippedList, MissingField.Ignore ),
#”Removed Columns” = Table.RemoveColumns(RenamedColumns,{“Column5″}),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Removed Columns”, {“Store”, “Delivery from”}, “Attribute”, “Value”),
#”Renamed Columns” = Table.RenameColumns(#”Unpivoted Other Columns”,{{“Attribute”, “Файл/Услуга”}, {“Value”, “Брой”}}),
#”Filtered Rows” = Table.SelectRows(#”Renamed Columns”, each ([Брой] 0) and ([Store] “TOTAL”))
in
#”Filtered Rows”
Today also tried to find solution, but failed. 🙁
Can you help me with advice?
Best regards
Todor Todorov
Hi again Ivan,
I found where is the problem – probably symbols [] is not allowed to be used in query.
After replacing these symbols, query is working again.
You can delete both posts, to solve my spam in your blog!
Sorry for losing your time!
Best regards
Todor Todorov