none
Sum by group in Power Query RRS feed

  • Question

  • I have a table of data and I need to do a sum by group (I have two group by columns). The result should be a new column added to the table and give me the summation of the sold products for each of the locations (ship DC),  product category  and customer. For this purpose, I wrote this code as a formula in my new column:

    Table.Group(#"Removed Columns", {"Product Category", "Ship DC", "Customer"}, {{"sold products", each List.Sum([#"QTY Sold"]), type number}})

    However, what I get is a table in each row which will not work for me as I need the result from this step as an input for my next step's calculation. I tried to expand, but it expanded all the cells in the table, not only the result. So, I had a lot of duplications. Is there any way I can get each value in each row rather than the whole table in each row. Thanks in advance for help.

    Tuesday, January 29, 2019 9:22 PM

Answers

  • Hi

    If question/problem understood - assuming data in Table1 and at stage #"Change Type" you have:

    … Select column [Product Category] > Right-click > Group by… > check Advanced > Add the other 2 ('Ship DC' & 'Customer') grouping fields > …> Operation: Sum; Column: QTY Sold:

     

    Corresponding code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,
            {{"Product Category", type text}, {"Ship DC", type text}, {"Customer", type text}, {"QTY Sold", type number}}),
        #"Grouped Rows" = Table.Group(#"Changed Type",
            {"Product Category", "Ship DC", "Customer"},
            {{"Sold_products", each List.Sum([QTY Sold]), type number}})
    in
        #"Grouped Rows"

    If problem misunderstood please upload a screenshot showing how you table looks like before you try to aggregate + one showing the expected result.

    If this solves your problem please Mark as Answer ==> Can help others - Thanks 

    Tuesday, January 29, 2019 10:22 PM

All replies

  • Hi

    If question/problem understood - assuming data in Table1 and at stage #"Change Type" you have:

    … Select column [Product Category] > Right-click > Group by… > check Advanced > Add the other 2 ('Ship DC' & 'Customer') grouping fields > …> Operation: Sum; Column: QTY Sold:

     

    Corresponding code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,
            {{"Product Category", type text}, {"Ship DC", type text}, {"Customer", type text}, {"QTY Sold", type number}}),
        #"Grouped Rows" = Table.Group(#"Changed Type",
            {"Product Category", "Ship DC", "Customer"},
            {{"Sold_products", each List.Sum([QTY Sold]), type number}})
    in
        #"Grouped Rows"

    If problem misunderstood please upload a screenshot showing how you table looks like before you try to aggregate + one showing the expected result.

    If this solves your problem please Mark as Answer ==> Can help others - Thanks 

    Tuesday, January 29, 2019 10:22 PM
  • No news means it didn't work, correct? ;-)
    Saturday, February 2, 2019 3:29 PM