Forum Discussion

MarkPalmberg's avatar
MarkPalmberg
Kudo 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 with variable column names and variable N of columns. Here's what my data looks like coming from the source:

And here's the code from the advanced editor after I add the pivot step:

let
Source = Sql.Database(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)
in
#"Pivot FY"

I've been looking at this to try to work through this, and I feel like I'm close, but I'm getting errors trying to generate the range of columns I need to sum from the #"Pivot FY" step...basically the number of columns starting at 1.

Thanks so much for any push you can provide!

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.

9 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You really should consider keeping your data unpivoted.  It will make your analysis and visualization simpler.  You can easily pivot the data out in the visual when needed (e.g., in a matrix visual).

     

    Pat

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Table.ColumnNames(Table) will give you a list of the column names, and Table.ColumnCount(Table) will give you the number of columns, as will List.Count(Table.ColumnNames).

     

    But I would probably do this. Right before Pivot, get the list of lists  the column names and types, so you can use them in your query and get your counts and all that. Add new step, name it "Items":


    = List.Zip({Table.Schema(Table)[Name], Table.Schema(Table)[TypeName]})

     

    This will give you your list of columns and their type, which when you know what columns you want, you can wrap the whole thing in List.Select, man, the possibilities are endless.

     

    --Nate

     

     

     

     

    • MarkPalmberg's avatar
      MarkPalmberg
      Kudo 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
        Community 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.