Forum Discussion
PQ Advanced Editor help for summing pivoted columns
Hi MarkPalmberg ,
Based on your description, you can try this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdFLDoUgDIXhvTB2IC3PobIM4/63ce9Bk57iqMkXLH/wusIRtiC7CEYL9+YkRhbFUJaEM5UlQwpLgbSPuM0Vo7O0z55uclJhXuUfRYLm1FnQrO4rNIvbPAv7Ku/7nNYc3V1oTpVlNj8vNqzw7SHRwoLmvLPMZifUPKxQZRVxd6E5uTPN/uCwZtx+/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Year = _t, Sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Year", Int64.Type}, {"Sales", Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Sales", List.Sum), #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Category"}, {{"Data", each _, type table [Category=nullable text, 2022=nullable number, 2023=nullable number, 2024=nullable number, 2025=nullable number, 2026=nullable number, 2027=nullable number, 2028=nullable number, 2029=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Total", each List.Sum(Table.Transpose(Table.RemoveColumns([Data],"Category"))[Column1]),type number), #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"2022", "2023", "2024", "2025", "2026", "2027", "2028", "2029"}, {"2022", "2023", "2024", "2025", "2026", "2027", "2028", "2029"}) in #"Expanded Data"To make the column sum be variable, you can modify this query code as your needed:
Table.RemoveColumns([Data],"Category") //if want to filter more rows, it could be like this: Table.RemoveColumns([Data],{"Category","column2",...}) //if just needs a few columns, use Table.SelectColumns() instead of Table.RemoveColumns(): Table.SelectColumns([Data],{"Category","column2",...})Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for this reply, v-yingjl . The only issue here is that, since I'm trying to sum *only* the columns that are the result of the pivot, I don't want the first column included in the calculation. Here's how the data is originally formatted from the source:
And after the pivot:
So I'm looking for the sum of all the columns *after* the first column. The columns names will be variable, so I don't want to refer to actual column names, and the number of columns to sum will be variable as well.
Hi MarkPalmberg ,
Based on your description, you can try this query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdFLDoUgDIXhvTB2IC3PobIM4/63ce9Bk57iqMkXLH/wusIRtiC7CEYL9+YkRhbFUJaEM5UlQwpLgbSPuM0Vo7O0z55uclJhXuUfRYLm1FnQrO4rNIvbPAv7Ku/7nNYc3V1oTpVlNj8vNqzw7SHRwoLmvLPMZifUPKxQZRVxd6E5uTPN/uCwZtx+/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Year = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Year", Int64.Type}, {"Sales", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Sales", List.Sum),
#"Grouped Rows" = Table.Group(#"Pivoted Column", {"Category"}, {{"Data", each _, type table [Category=nullable text, 2022=nullable number, 2023=nullable number, 2024=nullable number, 2025=nullable number, 2026=nullable number, 2027=nullable number, 2028=nullable number, 2029=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Total", each List.Sum(Table.Transpose(Table.RemoveColumns([Data],"Category"))[Column1]),type number),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"2022", "2023", "2024", "2025", "2026", "2027", "2028", "2029"}, {"2022", "2023", "2024", "2025", "2026", "2027", "2028", "2029"})
in
#"Expanded Data"
To make the column sum be variable, you can modify this query code as your needed:
Table.RemoveColumns([Data],"Category")
//if want to filter more rows, it could be like this:
Table.RemoveColumns([Data],{"Category","column2",...})
//if just needs a few columns, use Table.SelectColumns() instead of Table.RemoveColumns():
Table.SelectColumns([Data],{"Category","column2",...})
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for this reply. The only issue is going to be that any hard-coded values for column names will cause the code to break when a new value appears.