Help - Query hasn't grouped properly

[PowerNovice]

Hi, 

Im very new to using PowerQuery..... my grouped rows hasnt grouped properly. Well to me it hasnt, but ive probably missed something. 

I am trying to group based on an order number, and then sum the quantity. In the below example, i have 12 items at a weight of 800lbs, and one item 700lbs. I have grouped by order number and created a new column to sum the quatity. However, it has only summed the 12 items together and did not include the 700lbs item. Thus, leaving a second line with the same order number. 

This doesnt work beacuse i will be using this data in to drive a PowerApps application page. 

Any help would be great. 

Please see below for live example. 

Thank you,

@PowerNovice 

[Syk · Accepted solution]

This happened because you included the 'AllocatedQuantity' in your group by. It looks like all of the other rows had a quantity of 800 so it grouped all of those together and grouped the one with 700 by itself.

To fix this, just go into the step where you grouped the columns and remove the 'Allocated Quantity' from the grouping!

[PowerNovice]

@Syk thank you for the support on this. This worked as you probably expected. 

Would you be able to explain why this was the problem. Im not sure i understand why this caused the above issue? 

Thank you

[Syk]

You want to group on the fields that you're not going to aggregate.

For example:

Category	Product	Sales
A	Toy	10
A	Shirt	20
A	Shirt	15

If I want to find out how much I've sold of each Category and Product I'd group those fields and aggregate (sum) the Sales column which would give me this.

Category	Product	Sales
A	Toy	10
A	Shirt	35

The grouping finds distinct combinations to perform your aggregation. That's why you had the issue, because you were including the Sales column in your grouping (which in this example, would be the exact same as the first table).

[PowerNovice]

Hi @Syk ,

Thank you for the explanation, it makes sense as to why it wasnt working now. 

I appreciate your help 🙂