Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
You should unpivot your 'country' tables in order to create a table that can be joined to the other table.
From there you can pivot this new table.
Thanks, @wintee for your reply.
I've tried that, but the moment that I repivot the table so I can then get each of the goals as a column, the relationship between label mapping breaks.
What am I missing here?
Can you post an example of what the expected output is? I cannot figure out how those two tables are related.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSure thing, I am expecting this output for each country table:
US
Date | Sessions | Users | Blue | Red | Yellow | etc |
04/06/19 | 359 | 252 | 32 | 21 | 3 | etc |
etc |
GB
Date | Sessions | Users | Red | Yellow | Green | etc |
04/06/19 | 34 | 145 | 31 | 16 | 3 | etc |
etc |
AU
Date | Sessions | Users | Green | Blue | Red | etc |
04/06/19 | 42 | 175 | 12 | 14 | 1 | etc |
etc |
And so on.
Does this make sense?