Forum Discussion
Dynamically update column names
I've got a series of tables that all look the same for different countries, I.e.:
| Date | Sessions | Users | Goal 1 | Goal 2 | Goal 3 | etc |
| 04/06/19 | 359 | 252 | 32 | 21 | 3 | etc |
| etc |
I've then got another table with a set of standardised labels that I want to replace with their respective column names in their individual country tables. The issue is that the column names don't match up with each country's table and changing it is not an option. You can see the issue in the column name table here:
| Country | Goal | Label |
| US | Goal 1 | Blue |
| US | Goal 2 | Red |
| US | Goal 3 | Yellow |
| GB | Goal 1 | Red |
| GB | Goal 2 | Yellow |
| GB | Goal 3 | Green |
| AU | Goal 1 | Green |
| AU | Goal 2 | Blue |
| US | Goal 3 | Red |
How would I get it so that each label is dynamically applied as the column name for each country's table and the goal's respective column?
Any help in getting this up and running for a Power Query & M newbie is much appreciated.
Hi Anonymous ,
You need to unpivot each country table, then, add a custom column to lookup to [Label] field from the summary table. At last, pivot country table.
To reference to column in another table, please see below M function.
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each (let currentGoal = [Goal] in Table.SelectRows(TB1, each [Country] = "GB" and [Goal] = currentGoal)){0}[Label]),The whole M code in advanced editor is like below.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Sample Data.xlsx"), null, true), GB_Sheet = Source{[Item="GB",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(GB_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Sessions", Int64.Type}, {"Users", Int64.Type}, {"Goal 1", Int64.Type}, {"Goal 2", Int64.Type}, {"Goal 3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Sessions", "Users"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Goal"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each (let currentGoal = [Goal] in Table.SelectRows(TB1, each [Country] = "GB" and [Goal] = currentGoal)){0}[Label]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Goal"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"I have uploaded the sample .pbix file for your reference.
Best regards,
Yuliana Gu
6 Replies
Hi Anonymous ,
You need to unpivot each country table, then, add a custom column to lookup to [Label] field from the summary table. At last, pivot country table.
To reference to column in another table, please see below M function.
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each (let currentGoal = [Goal] in Table.SelectRows(TB1, each [Country] = "GB" and [Goal] = currentGoal)){0}[Label]),The whole M code in advanced editor is like below.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Sample Data.xlsx"), null, true), GB_Sheet = Source{[Item="GB",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(GB_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Sessions", Int64.Type}, {"Users", Int64.Type}, {"Goal 1", Int64.Type}, {"Goal 2", Int64.Type}, {"Goal 3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Sessions", "Users"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Goal"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each (let currentGoal = [Goal] in Table.SelectRows(TB1, each [Country] = "GB" and [Goal] = currentGoal)){0}[Label]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Goal"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"I have uploaded the sample .pbix file for your reference.
Best regards,
Yuliana Gu
Awesome, thanks for that, v-yulgu-msft . That worked!
Can you post an example of what the expected output is? I cannot figure out how those two tables are related.
