Forum Discussion

Anonymous

Not applicable

3 years ago

Grouping Columns

I've got a table that organizes like the following: Date Type A1 A2 B1 B2 B3 1/1/2022 Red 2 3 1 3 3 1/2/2022 Blue 3 2 2 0 2 1/3/2022 Green 2 3 3 3 1 I ...

Anonymous

Not applicable

3 years ago

Are these columns name (Ax, Bx) going to be fixed or dynamic?

Why not Power Query?

Anonymous

Not applicable

3 years ago

I have no problem with PQ, I just started the process in DAX, so that was the direction I figured I'd ask for. But if you know the solution using PQ, please let me know!

Anonymous
Not applicable
3 years ago
Here's PQ:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEKSk0BkiCWMRAbQmljpVgdkDIjmDKnnNJUqJwRFBuAaYg6Y5g696LU1DwkA43hBsfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, A1 = _t, A2 = _t, B1 = _t, B2 = _t, B3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Type"}, "Attribute", "Value"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}, {"Value", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Type", "Attribute.1"}, {{"Total", each List.Sum([Value]), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute.1]), "Attribute.1", "Total", List.Sum)
in
#"Pivoted Column"