Change typo column names using list

[primolee]

Hello everyone,

I am importing a standardized-format excel but a lot of times people will type the wrong column names causing errors.

I was thinking of creating lists for each and every column names with common typo words.

1. Compare column names to each list one by one
2. Whenever there is a match, it will rename the column using the first item in the list

For example, I have a list containing Date, date, day, Day.  I want to compare all column names to this list, if a colume name is matched, Table.RenameColumns will be performed to that column name replacing with the first item in the list which would be "Date".

However, is there a way to compare column names to lists?  Does anyone have a good solution to this problem?

Attached is the sample I created.  Thank you so much in advance.

https://drive.google.com/file/d/1cc6ktVA2iL-PP31Xzv0hi6_rchukaiGT/view?usp=sharing 

[Vera_33 · Accepted solution]

Hi 

Paste it to Advanced Editor to your wrong table, made a little change so you don't need to include Upper case in your Lists

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjIwMtA31DdU0lFySSzLTAHSKYllqQ7puYmZOXrJ+blKsTpQVUZgVcGpiXlAqhhIYVNkDFbklZqWBqSygBSyolgA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Day = _t, name = _t, #"e-mail" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Day", type date}, {"name", type text}, {"e-mail", type text}}
  ),
  Original = Table.ColumnNames(#"Changed Type"),
  New = List.Transform(
    Original,
    each [
      a = Text.Lower(Text.Trim(_)),
      b = 
        if List.Contains(DateList, a) then
          "Date"
        else if List.Contains(NameList, a) then
          "Name"
        else
          "Email"
    ][b]
  ),
  Custom = Table.RenameColumns(#"Changed Type", List.Zip({Original, New}))
in
  Custom

[primolee]

Hello @Vera_33 

Thank you so much for your reply, this is exactly what I want, thank you!

I made a little change again as your codes will hard-code the column name, I make it to rename using the first item in the list.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjIwMtA31DdU0lFySSzLTAHSKYllqQ7puYmZOXrJ+blKsTpQVUZgVcGpiXlAqhhIYVNkDFbklZqWBqSygBSyolgA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Day = _t, name = _t, #"e-mail" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Day", type date}, {"name", type text}, {"e-mail", type text}}
  ),
  Original = Table.ColumnNames(#"Changed Type"),
  New = List.Transform(
    Original,
    each [
      a = Text.Lower(Text.Lower(_)),
      b = 
        if List.Contains(DateList, a) then
          List.First(DateList)
        else if List.Contains(NameList, a) then
          List.First(NameList)
        else
          List.First(EMailList)
    ][b]
  ),
  Custom = Table.RenameColumns(#"Changed Type", List.Zip({Original, New}))
in
  Custom

Once again, thank you!

Best regards,

David 

[Vera_33]

Hi @primolee 

Yes, cool, David, actually I made one typo in the code, I was intended to Trim and Lower the text, but Lower twice🤣

[primolee]

ohoh~  I didn't notice that double lower case, thanks for the reminder!  😀  However on the second thought, trimming is not necessary, some people actually add extra space as typo... 🤣

[mahoneypat]

I think a better approach would be to use relative references to rename that column (or >1 column) to the correct name regardless.  See this video for how to do it.

(6) Power BI - Use relative references to avoid combine & transform errors - YouTube

Pat

[primolee]

Hello @mahoneypat ,

Thank you so much for the youtube link.

However, that will only work if columns are in the exact order.  If the column order changes, it will cause the wrong column being renamed.

This is why I was trying to figure out a different approach of doing so.

Best regards,

David