Dynamically update column names

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