Forum Discussion

primolee's avatar
primolee
Helper V
5 years ago
Solved

Change typo column names using list

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 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

     

6 Replies

    • primolee's avatar
      primolee
      Helper V
      5 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      primolee
      Helper V
      5 years ago

      Hello Anonymous 

       

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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🤣