Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
PowerNovice
New Member

Help - Query hasn't grouped properly

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. 

 

PowerNovice_1-1709044033527.png

 

Thank you,

@PowerNovice 

 

1 ACCEPTED SOLUTION
Syk
Super User
Super User

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!

View solution in original post

4 REPLIES 4
Syk
Super User
Super User

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!

@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

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

Hi @Syk ,

 

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

 

I appreciate your help 🙂

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.