Forum Discussion
Conditional change to column name? - inconsistent source naming
Hi,
I'm working with excel report output as my Power Query report input (not fun), and my report needs to be able to consume old reports as well as recent reports. Today I was hit with a surprise name change to a column name in today's run of the source report.
Is there a way I can insert a conditional step in the advanced editor, where I detect whether the report is using the old name, then update the column name only if it is present? I could do this by checking the column names themselves or by checking the naming convention on the source file, since it includes the run date and I know the date the change occurred.
Final solution --
I removed this clause from the code:
each Text.Contains(_, "ChangeThisSubstring"))),
This is the condition that was causing the failure, since my source data didn't always contain any instances of "ChangeThisSubstring". With the condition removed, this meant that my "#Added Custom" column name replacement table now had a line for every column in my data, not just the columns that needed replacing -- but for other columns, the "new" name was the same as the old name since the Text.Replace rules didn't affect them.
Then, realizing that {0} was an index for which column to replace, I hardcoded the column I wanted to update using the index for that column:
Rename = Table.RenameColumns(#"Promoted Headers", Record.ToList(Table.ToRecords(#"Added Custom"){8}))
Now, the name for the index is always "changed" to the Text.Replace version every time data is loaded. If a column name is needed, it's changed to the updated version. If it's already the updated version, it just changes to a copy of the original name.
10 Replies
Yes, there are Power Query functions to list column names.
https://docs.microsoft.com/en-us/powerquery-m/table-columnnames
You can evaluate the list and then decide if any of them need renaming.
You also want to use Table.SelectColumns rather than Table.RemoveColumns when choosing what to keep.
thanks, I will take a look at this and report back.
Hi, I have a partial solution based on your comment and on this thread: https://community.powerbi.com/t5/Desktop/Rename-Columns-with-IF-formula/td-p/325573
However, this solution only works when the column I want to rename is present in the source table. What if I need to consume an earlier version of the source data where no rename is necessary? Basically, my case is that I have to swap between different versions of the underlying Excel data, and they changed the name of the column for recent tables. I think I need to check whether the #"Added Custom" table is null (in the code below), and if it is, I need the code to skip the subsequent Rename step.
Here's my code:
let
Source = Excel.Workbook(File.Contents(FolderPathParameter&FileParamater), null, true),
#"BA Level_Sheet" = Source{[Item="SourceExcelTab",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"SourceExcelTab", [PromoteAllScalars=true]),
#"Get Columns Names" = Table.FromList(List.Select(Table.ColumnNames(#"Promoted Headers"),each Text.Contains(_, "ChangeThisSubstring"))),
#"Added Custom" = Table.AddColumn(#"Get Columns Names", "Custom", each Text.Replace([Column1],"ChangeThisSubstring","NewSubstring") ),
Rename = Table.RenameColumns(#"Promoted Headers", Record.ToList(Table.ToRecords(#"Added Custom"){0}))in
Renametwo options
1. use the Table.Columnnames list to find out if a particular column is present or not.
2. Familiarize yourself with the try ... otherwise ... concept in Power Query. Then brute force your way through the rename.
