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.
Hi All,
Trust you are well!
I am new to this forum and I have searched for similar approaches to the issue below I am facing but could not find it here!
It goes like transforming the first table below into the second table using Power Query or M Languange.
it basically sums all the values or records from second column onwards (time frame) of the name identifiers A, B, C, and so on.
Your feedback, suggestion is very much appreciated!
FROM THIS | ||||||||||||||||
Date | 01-Jan | 02-Jan | 03-Jan | 04-Jan | 05-Jan | 06-Jan | 07-Jan | 08-Jan | 09-Jan | 10-Jan | 11-Jan | 12-Jan | 13-Jan | 14-Jan | 15-Jan | etc |
A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
A | 1 | 1 | 1 | 1 | ||||||||||||
B | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||
B | 1 | 1 | 1 | 1 | ||||||||||||
B | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
C | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
C | 1 | 1 | 1 | 1 | ||||||||||||
C | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||
C | ||||||||||||||||
D | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
etc | ||||||||||||||||
TO THIS | ||||||||||||||||
Date | 01-Jan | 02-Jan | 03-Jan | 04-Jan | 05-Jan | 06-Jan | 07-Jan | 08-Jan | 09-Jan | 10-Jan | 11-Jan | 12-Jan | 13-Jan | 14-Jan | 15-Jan | etc |
A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 |
B | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | ||||||
C | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
D | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
etc |
Solved! Go to Solution.
Unpivot and the re-pivot using sum as the aggregation.
Select the Date column and click Unpivot Columns > Unpivot Other Column in the Transform tab:
Select the Attribute column and click on Pivot Column. Choose the Value column as the Values Column.
Result:
Sample M query you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKEYgWlWB2IiAIUG4JFnIgUQTXHGasIqi78IhBdLkjmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"01-Jan" = _t, #"02-Jan" = _t, etc = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"01-Jan", Int64.Type}, {"02-Jan", Int64.Type}, {"etc", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"
That has worked fantastically! thanks
Unpivot and the re-pivot using sum as the aggregation.
Select the Date column and click Unpivot Columns > Unpivot Other Column in the Transform tab:
Select the Attribute column and click on Pivot Column. Choose the Value column as the Values Column.
Result:
Sample M query you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKEYgWlWB2IiAIUG4JFnIgUQTXHGasIqi78IhBdLkjmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"01-Jan" = _t, #"02-Jan" = _t, etc = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"01-Jan", Int64.Type}, {"02-Jan", Int64.Type}, {"etc", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"
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.