Forum Discussion
Renaming Table Headers with combined variable IDS as column headers with ID names from another table
- Anonymous2 years ago
Thank you all for the ideas shared, I appreciate it.
I have managed to solve this by creating a lookup table with coded names and full-text names and then renaming the columns using Table.RenameColumns() function.
Please copy the following queries to your Power BI Desktop to see the example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJR8kvMTTVUitUB8Y2gfCMo3xjKN4byTaB8E6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {[ID], [Name]}, type list),
List = #"Added Custom"[List]
in
List
--------------------------------------------
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUKg2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, ID1 = _t, ID2 = _t, ID3 = _t, ID4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"ID1", type text}, {"ID2", type text}, {"ID3", type text}, {"ID4", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",Translation)
in
#"Renamed Columns"
This is the result. I have a translation table:
That I transform to List of Lists:
Then, I use the translation list as a list argument in RenameColumns step:
I hope it helps.
- dufoq32 years ago
Community Champion
Hi PawelWrona, no need to use custom column. Use this as 3rd step of first table to create column name pairs.
Table.ToRows(#"Changed Type")- PawelWrona2 years ago
Resolver II
Thanks for the tip. Indeed, I haven't been using this functiong for a while..
- dufoq32 years ago
Community Champion
You're welcome. That's also the purpose of this forum - to learn 🙂