Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
7 years ago
Solved

Rename all column headers removing part before to separator

Hello to all,   I have a table with headers of the form   Table.text1.text2.text3:HeaderName1 Table.text1.text2.text3.text4.text5:HeaderName2 Table.text1.text2:HeaderName3 I´d like to remane a...
Need Help
  • cgkas's avatar
    cgkas
    7 years ago

    Thanks for your help and time Maggi v-juanli-msft  and mussaenda ,

     

    I found the solution combining Text.AfterDelimiter() with List.Zip() and List.Transform in a single command like below, maybe could help someone in the future.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RenameColumns = Table.RenameColumns( Source, List.Zip( 
                      { 
                        Table.ColumnNames( Source ), 
                        List.Transform(Table.ColumnNames( Source ), each Text.AfterDelimiter( _, ":") ) 
                       } ) )
in
    RenameColumns

    Regards

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Are you looking for a generic code which will go through all the field names of any input table and correct the field names dynamically without you specifying the field names explicitly?

 

OR

 

The table and the field names are fixed and you want a code which works on the specific table only?

  • cgkas's avatar
    cgkas
    Icon for Helper V rankHelper V
    7 years ago

    Hello. Yes I would like to rename all headers dynamically.

     

    I saw this function text-afterdelimiter

     

    Maybe a combination of 

    Table.TransformColumnNames() or Table.RenameColumns() with Text.AfterDelimiter()

     

    so the idea would be to get only the text after the delimiter ":" but I don´t know how to do it.

     

    Sample table would be like this:

     

    +-------------------------------------+-------------------------------------------------+-------------------------------+
| Table.text1.text2.text3:HeaderName1 | Table.text1.text2.text3.text4.text5:HeaderName2 | Table.text1.text2:HeaderName3 |
+-------------------------------------+-------------------------------------------------+-------------------------------+
| 1                                   | 2                                               | 4                             |
+-------------------------------------+-------------------------------------------------+-------------------------------+
| 7                                   | 3                                               | 4                             |
+-------------------------------------+-------------------------------------------------+-------------------------------+
    • mussaenda's avatar
      mussaenda
      Icon for Community Champion rankCommunity Champion
      7 years ago
      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYhOlWJ1oJXMgyxjCiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#" Table.text1.text2.text3:HeaderName1" = _t, #"Table.text1.text2.text3.text4.text5:HeaderName2" = _t, #"Table.text1.text2:HeaderName3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" Table.text1.text2.text3:HeaderName1", Int64.Type}, {"Table.text1.text2.text3.text4.text5:HeaderName2", Int64.Type}, {"Table.text1.text2:HeaderName3", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"HeaderName1", Int64.Type}, {"HeaderName2", Int64.Type}, {"HeaderName3", Int64.Type}})
in
    #"Changed Type3"

      Hi, please see the applied steps above.

      Below is the screenshot after transformation using your provided sample data.

       

      • cgkas's avatar
        cgkas
        Icon for Helper V rankHelper V
        7 years ago
        Thanks for your help mussaenda. But I'm still think this could be reached with a single command.