Forum Discussion
Helper VRename all column headers removing part before to separator
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 RenameColumnsRegards
Helper VHello. 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 | +-------------------------------------+-------------------------------------------------+-------------------------------+
Community Championlet
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.
- cgkasThanks for your help mussaenda. But I'm still think this could be reached with a single command.
v-juanli-msftCommunity Support
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
MaggieCommunity 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
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 RenameColumnsRegards