Dynamic column name based on a column value coming from a different unrelated table

Hi Anonymous - Associating the columns from Table A with the values from Table B, you can do in power Query as below:

Please follow the steps:

On table B:

Do the pivot :and I named it as TableB_Pivoted

On Table A, you can perform Merge query by joining the Table A with TableB_Pivoted use left outer join

Expand all 4 columns , you can see the below image:

Click on Add Column -> Custom Column.
Name the column Column1 - Extract Date and use the following formula

TableB_Pivoted[Column1]{0} 

TableB_Pivoted[Column2]{0}

TableB_Pivoted[Column3]{0}

TableB_Pivoted[Column4]{0}

Repeat this for all columns in Table A (Column2, Column3, Column4).

Perform last few steps:

Do a pivot on Table A with Selecting Column 1 

do it for remaining 3 columns too

I am not able to share pbix file here, not sure how to share it here.

i will add the complete advanced query editor

Please follow the steps you can achieve the same.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi Anonymous ,

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY85DoAwDAT/4prC3LSEMomUkkNpQIIe+L9YCE6mWEujabws1DPnlFGvBmzOzDi3QD77igJynGZsGYpDkKKEVEqlYhOkqCC11tgmFGtEkhrWGIOtQ3JFJGlgnXPYLiTnjwQtnLX2f+Yt9gR5/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}}),
    Custom1 = Table.FromList(Table.ColumnNames(#"Changed Type")),
    #"Merged Queries" = Table.NestedJoin(Custom1, {"Column1"}, TableB, {"Column Name"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"Extract Date"}, {"Extract Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "Names", each [Column1] & " - " & [Extract Date]),
    Custom2 = Table.Group(#"Changed Type",{"Column1"},{{"Data",each _}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom2, "Data", {"Column1", "Column2", "Column3", "Column4"}, #"Added Custom"[Names]),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Column1"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Hi,

In TableB, create this custom culomn formula

The M code in Table A will be

let
Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
Custom1 = Table.RenameColumns(Source,List.Zip( { TableB[Column Name], TableB[Merged]}),MissingField.Ignore )
in
Custom1

Hope this helps.