# Sum by group in Power Query

• ### 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

• 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.

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.