Forum Discussion

DmitryKo's avatar
DmitryKo
Helper IV
2 years ago

Rename columns based on pre-defined name map table

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 ...
Need Help
DmitryKo's avatar
DmitryKo
Helper IV
2 years ago

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's avatar
AlexisOlson
Super User
2 years ago
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