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

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.
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi cgkas 

We need more steps than a single command to solve this problem. (even using the suggestion Text.AfterDelimiter)

Steps are as below:

1. add an index column

2. click on index column, unpivot other columns

3. add a custom column

4. remove column "Attribute",

click on "Custom" column, select "pivot column"

 

Code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYjOlWJ1oJSMgywSILZViYwE=", 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}}),
    #"Added Index1" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index1", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.AfterDelimiter([Attribute],":")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in
    #"Pivoted Column"

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • cgkas's avatar
    cgkas
    Icon for Helper V rankHelper V
    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