Conditional change to column name? - inconsistent source naming

[McSarah]

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.

[lbendlin]

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.

[McSarah]

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
      Rename

[McSarah · Accepted solution]

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.

[lbendlin]

Note that {8}  is a row reference, not a column reference.

[McSarah]

Yes, I guess that's right -- but it's a row reference to the custom table of column names. Anyway, when I specified the correct (row) column name in the script, it correctly swapped the correct (column) column name in the outer table. Maybe there are nuances I don't fully understand, but this appears to work.

[lbendlin]

two 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.

[McSarah]

sounds promising, thank you. I will look at this.

[mahoneypat]

Learning about try ... otherwise will definitely be helpful.  FYI that there is an optional paramter too in rename columns called MissingField.Ignore that may also be useful (i.e., no error if you try to rename a column that isn't there).

https://docs.microsoft.com/en-us/powerquery-m/table-renamecolumns

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[McSarah]

Thanks for helping with this. I got this working by applying my Text.Replace logic to all column names, not just those that actually have the substring that needs changing (removing the clause that specifies getting only columns with the substring). With this change, my custom table is never null/ an error is never thrown, and the custom table that processes the name change (OldName, NewName) simply has a duplicate of the OldName whenever the substring is not present. Last, I specified the column index of the columns to "change" to the NewName.

However, I'm interested in whether you think try... otherwise would have been a better approach.

[McSarah]

thanks, I will take a look at this and report back.