Rename columns based on pre-defined name map table

[DmitryKo]

Say we have a source with some arbitrary named columns (Table 1). During transformation, columns in Table 1 need to be renamed based on a name map similar to this:

Source name	Target Name
Status	State
Category	Type
...	...

Say we have this name map stored somewhere in PBI-reachable source, e.g. simple Excel table as Table 2.

Is there a way to achieve such rename scenario in M?

[AlexisOlson]

Yes! You can use List.Zip on the mapping table columns to generate a list of column renaming pairs.

For example, if your mapping table is named Map, then the renaming step like this

Table.RenameColumns(Source,{{"Status", "State"}, {"Category", "Type"}})

can be made dynamic like this:

Table.RenameColumns(Source, List.Zip({Map[Source name], Map[Target Name]}))

[DmitryKo]

Presense of every column listed amone "source" is not guaranteed in a given table. I assume this code would raise an error in this case? Usually when trying to manupulate the columns that do not exist, M does that.

The business scenario behind is similar to the following. We're processing data exports from various systems of the same type - say, task trackers. Depending on the source system's vendor, locale settings used during export, customizations and other factors, the datasource might or might contain fields with specific names.

At the same time, nature of the dataset would remain pretty much the same, e.g. it would always contain a field representing item's status (be it named "Status", "State", "Stage" etc), some timestamp-related fields, typical scalar attributes (e.g. priority). It might or might not contain certain non-mandatory fields - description, links, whatever.

During data transformation, we need to generalize the dataset in order to get the data in the format that is expected by the model - e.g. item's status is always in "Status" field, which is used in measures/reports, and so on. Hence the need for field renames based on a set of rules managed outside of the model itself.

[AlexisOlson]

---

@DmitryKo wrote:

Presense of every column listed amone "source" is not guaranteed in a given table. I assume this code would raise an error in this case? Usually when trying to manupulate the columns that do not exist, M does that.

Yes, if Source doesn't contain "Status" then, you'd get an error with the code I suggested. It's possible to address that though with an extra filter.

Here's a sample query:

let
  Map = Table.FromRows(
    {{"Status", "State"}, {"Stage", "State"}, {"Category", "Type"}}, 
    type table [#"Source name" = Text.Type, #"Target Name" = Text.Type]
  ), 
  Source = Table.FromRows(
    {{"Good", "A", 1}, {"Bad", "B", 2}}, 
    type table [Status = Text.Type, Type = Text.Type, OtherCol = Int64.Type]
  ), 
  ValidReplacments = Table.SelectRows(
    Map, 
    each List.Contains(Table.ColumnNames(Source), [Source name])
  ), 
  ReplaceColNames = Table.RenameColumns(
    Source, 
    List.Zip({ValidReplacments[Source name], ValidReplacments[Target Name]})
  )
in
  ReplaceColNames