Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Perhaps @ImkeF or someone can help me out with this one. I have a work-a-round solution but would really prefer to solve this in Power Query if possible.
I have a table in Excel that is basically:
Metric, Actual, Budget, Prior Year
Metric1,100,200,150
Metric2,50,100,75
...
What I want to end up with is:
Metric1 Actual, Metric1 Budget, Metric1 Prior Year, Metric2 Actual, Metric2 Budget, Metric2 Prior Year
100,200,150,50,100,75
Any clean way of doing that in Power Query through some fancy unpivots, etc?
Solved! Go to Solution.
Something like this:
let Source = Table1, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Metric"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Metric", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"
Created via standard menu options; the last step with aggregation "don't aggregate".
Something like this:
let Source = Table1, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Metric"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Metric", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"
Created via standard menu options; the last step with aggregation "don't aggregate".
Yep, thanks @MarcelBeug, that got me there. It always seems so easy and obvious when you and @ImkeF show me this stuff!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |