Renaming Table Headers with combined variable IDS as column headers with ID names from another table

[tkavuma]

I have two tables, Table 1 contains columns which are derived by combining two categories joined by underscore. 

Table 2 have the identicle IDs with their text names. 

I want to replace each ID with the ID name so that the eader is a combination of two ID names.

Tabel2Table1

[tkavuma · Accepted solution]

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.

[PawelWrona]

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.

[dufoq3]

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")

[PawelWrona]

Thanks for the tip. Indeed, I haven't been using this functiong for a while..

[dufoq3]

You're welcome. That's also the purpose of this forum - to learn 🙂

[lbendlin]

There's a dedicated function for that. Table.RenameColumns - PowerQuery M | Microsoft Learn

[tkavuma]

Hey @lbendlin Thanks for the insight. I would like to know how I can pass the second table as an argument in the Table.RenameColumns() function.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ToddChitt]

How many columns are in Table1? Table2 has 1300 rows, are they all used to create columns in Table1?

Is there a unique ID for Table1?

What if you did an UNPIVOT of Table1 that yielded the following:

Unique ID, Column Name, Value

Next, split the Column Name (ABC123_DEF456) by the underscore character, so now you have:

Unique ID, Column Name before the underscore, Column Name after the underscore, value.

Now do a JOIN to Table2 on [Column Name before the underscore] joined to Table2 id. Join again for [Column Name after the underscore]. 

Expand the joined instances of Table2 to get the name(s) column.

Combine the two Table2.Names with an underscore between them.

Remove everything but the Unique ID, Value, and new Column Name.

Lastly, re-pivot the data.

Keep in mind this is 'air-ware' and may not actually work with your data. It works in my mind, though 🙂

[tkavuma]

Hey @ToddChitt , I have 15 tables that I am querying from a database the 1300 rows in Table 2 are categories used across other tables by combining two categories as headers.

Let me try your suggestion, I will revert with feedback.