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

[Brij]

Hi,

I have a requirement to get the column name (for Table A) that can change dynamically based on column value that is coming from a different table (Table B) which is not related to this table (Table A).

Note: Table A and Table B are not related with each other and there is no common identifier between then except the column names from Table A and the "Column Name" column value from Table B.

Please let me know how to achieve this? 

Here is the mock up and end result.

Table A

Column1	Column2	Column3	Column4
A001	ABC	1000	ttttttttt
A002	XYZ	3000	ggggggggg
A003	BBB	3000	ccccccccc
A004	KKK	6000	bbbbbbbbbb
A005	LLL	5000	ssssssssss
A006	PPP	8000	rrrrrrrr
A007	MMM	10000	fffffffffff

Table B

Column Name	Extract Date
Column1	22/05/2024
Column2	4/06/2024
Column3	30/04/2024
Column4	6/06/2024

Expected End result

Column1 - 22/05/2024	Column2 - 4/06/2024	Column3 - 30/04/2024	Column4 - 6/06/2024
A001	ABC	1000	ttttttttt
A002	XYZ	3000	ggggggggg
A003	BBB	3000	ccccccccc
A004	KKK	6000	bbbbbbbbbb
A005	LLL	5000	ssssssssss
A006	PPP	8000	rrrrrrrr
A007	MMM	10000	fffffffffff

[Ashish_Mathur]

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.

[Brij]

Hi @Ashish_Mathur 

This is working perfectly. However, every time we refresh the data (that changes the extract date for a particular column). It breaks the visual that these columns are listed such as on a table visual! 

Any thoughts why is the case?

Any help is appreciated.

Thank you

Brij

[Ashish_Mathur]

You are welcome.  I obviously will not be able to understand any reason from your sentence.  Share a lot more details.

[Brij]

Hi @Ashish_Mathur 

Sorry if I was not clear enough in my ask.

From my original requirements, you provided a solution that is working fine and I can attach the dates that is coming from Table B to Table A's column names. Such as "Column1 - 22/05/2024" and we are presenting these columns in a Table visual on the report page like what I have shown in "Expected End result" in my original post.
However, as soon as we refresh the data tables (Table A and Table B), obviously the value in the "Extract Date" column from Table B will change and it also reflecting

correctly on the TableA as well. e.g.

Before Refresh

Current Extract Date (Table B) value for Column1 = 22/05/2024 Hence the header for Column1 in Table A would change to "Column1 - 22/05/2024"

After Refresh  

After refresh Extract Date (Table B) value for Column1 = 10/06/2024 Hence the header for Column1 in Table A would change to "Column1 - 10/06/2024"

However, I'm presenting these columns (From Table A) on the "Table" visual on the report page and as soon as I refresh the report, it should replace the column1 value from  "Column1 - 22/05/2024" to "Column1 - 10/06/2024", instead it just showing me error for that visula with "Fix value" link and upon clicking on the link, this (and any other column that has a new associated dates) column disappears from the table visual!

The table visul would look like as per below after table refresh that changes extract date value for the column1 and column2. (These two column disappear from the table visual - I kept it in dark grey area just to show you how it should appear).

I hope this time it makes sense.

Please let me know if you can why is this happening?

Thank you,

[Ashish_Mathur]

I do not know why this is happening.

[Anonymous]

Hi @Brij ,

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

[rajendraongole1]

Hi @Brij - 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!!

[Brij]

Thanks @rajendraongole1 

I have tried to follow the steps you provided, however, I'm getting errors when applying the step - adding columns "Column1 - Extract Date". See below

It would be great if you could attach pbix so I can check where I am making mistake or if you could explian please?

Thanks

Brij

[rajendraongole1]

Hi @Brij - I am not able to attached my pbix file here,option not available.

you almost reached towards the solution. 

after merge queries, during the expanding option you can remove the original prefix and continoue to add the new custom columns, it works

Check and confirm

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

[Brij]

Thanks, @rajendraongole1 for your help.

I was keeping the original prefix hence it was throwing an error! But after removing it I can move forward.

I am almost there. Now I can see the dates as the column header for the relevant columns however, the original column labels is not appearing along with the dates! I can see it on your last step, but how did you get it? Pleaes advise. Thanks

[rajendraongole1]

Great @Brij - if it works, mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

last step you can rename it.

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

[Brij]

Hi @rajendraongole1 

So it need to be changed manually?

And yes, I will mark this as solution.

Please let me know.

Thanks

Brij

[rajendraongole1]

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

[rajendraongole1]

Hi @Brij - yes, we have to change it manually to rename. 

Thank you @Brij 

if you acheive the solution.please mark it.

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

[Brij]

Thanks @rajendraongole1 

When I rename it manually after the last step "Removed columns" and then try to test it to change a date for one column (Column1) and then refresh both the table it is throwing an error and not replacing the column name!
Up to here it's all good and change the column1 date from 22/05/2024 to 11/06/2024

However, as soon as I add a step to rename from "11/06/2024" to "Column1 - 11/06/2024", it is throwing an error as per below!

Also, placing screenshot of the advance editor so you can see all the steps - 

Please advise.

Thanks

Brij

[rajendraongole1]

Hi @Brij - If possible, can you share the pbix file in one drive , i will check and upload it back to you. 

please check the last two steps, rename column before that are you able to see the data?

if yes, please share the snapshot?

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

[Brij]

Hi @rajendraongole1 

I am not able to upload the pbix for some reason!

Brij

[foodd]

You will need to reply with a sharing link from a cloud based service such as OneDrive, Google Drive, BOX, Dropbox or similar.