Forum Discussion

MarkPalmberg's avatar
MarkPalmberg
Icon for Kudo Commander rankKudo Commander
5 years ago
Solved

PQ Advanced Editor help for summing pivoted columns

Hi.   I'm just getting started with modifying steps in Power Query using the advanced editor. My first foray involves summing the columns that are the result of a pivot step that results in columns...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

MarkPalmberg's avatar
MarkPalmberg
Icon for Kudo Commander rankKudo Commander
5 years ago

Thanks so much for this reply (and yours, too, mahoneypat; I hear you, but this is for a specific table visual requested by the business, or I'd be "matrixing" it all day long).

 

So, I added 

 

ColCount = Table.ColumnCount(Source),

 

just before my pivot step and got, as expected, 3 (revenueloopukip, fiscalyear, fyamount).

 

I then added a copy of that step *after* my pivot step, which yields 10. Also makes sense.

 

Now, I'd like to add a column to my pivot table that's a sum (using List.Sum to handle nulls?) of columns 1 (recognizing 0 index) through the value returned by Table.ColumnCount(PivotStep) of my PivotStep table. 

 

Here's what the advanced editor looks like so far:

 

let
    Source = Sql.Database(SQL),
    SourceColumnCount = Table.ColumnCount(Source),
    #"Pivot FY" = Table.Pivot(Table.TransformColumnTypes(Source, {{"FISCALYEAR", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source, {{"FISCALYEAR", type text}}, "en-US")[FISCALYEAR]), "FISCALYEAR", "FYAMOUNT", List.Sum),
    PivotColumns = Table.ColumnCount(#"Pivot FY"),
    PivotColumnNames = Table.ColumnNames(#"Pivot FY")
in
    PivotColumnNames

 

 

EDIT: Also worth noting that I get this error when trying to "Close and Load" this query:

 

"We cannot convert a value of type List to type Table."

 

 

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
5 years ago

Hi MarkPalmberg ,

Table.ColumnNames() would return a list with table names, not a table but basically when close and apply it in power query, it should also have a table in power bi table view like this:

 

In addition, what is your expected output base on your sample picture, add a custom column to calculate the column count or anything else?

 

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.

  • MarkPalmberg's avatar
    MarkPalmberg
    Icon for Kudo Commander rankKudo Commander
    5 years ago

    Thank you for the reply. Yes, I'm trying to add a column that's a sum of the columns from the Table.Pivot step. Everything from the second column (index 1) through the last column, whatever it might be. Like so:

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi MarkPalmberg ,

      To calculate the pivot column total, you can try this query to add a custom column:

      = Table.AddColumn(#"Pivoted Column", "Custom", each List.Sum(Table.Transpose(#"Pivoted Column")[Column1]), type number)

       

      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.

      • MarkPalmberg's avatar
        MarkPalmberg
        Icon for Kudo Commander rankKudo Commander
        5 years ago

        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.