- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 all headers removing all the characters before the separator ":", so the new headers names would be:
HeaderName1 HeaderName2 HeaderName3
I've tried changing one header with this code:
= Table.RenameColumns(#"Step5",{{"Table.text2:HeaderName1", "HeaderName1"}})
But how can I rename all the headers in one step?
Thanks in advance
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, can you share sample data?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 | +-------------------------------------+-------------------------------------------------+-------------------------------+
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-02-2024 07:23 PM | |||
11-08-2023 07:59 PM | |||
07-08-2024 10:27 PM | |||
Anonymous
| 01-04-2022 04:34 AM | ||
08-12-2024 02:36 AM |
User | Count |
---|---|
123 | |
79 | |
59 | |
58 | |
44 |
User | Count |
---|---|
179 | |
120 | |
82 | |
70 | |
53 |