Forum Discussion

primolee's avatar
primolee
Icon for Helper V rankHelper 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 colum...
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

     

     

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
    Icon for Helper V rankHelper 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🤣

      • primolee's avatar
        primolee
        Icon for Helper V rankHelper V
        5 years ago

        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... ðŸ¤£