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.