Forum Discussion
Groupby with condition
Anonymous ,
If you would like to keep original table, in Power Query right click on table name and choose duplicate.
From now on working on duplicated query:
Go on home tabe and choose group by. Group by column Order Number and add 3 aggregations:
1) Items count - how many records per Order Number
2) Items = 0 - returns min value for Value. If there is value with 0 it will return 0. Based on this column you will hide/remove these rows
3) Sum amount - sum of volume
My demo screenshots below:
Filter column Items = 0 (uncheck 0 from list).
Filter column Items count - (uncheck 1 from list to get only Order numbers with multiple items)
Click save and apply and you have a new list of orders.
Anonymous ,
If you would like to keep original table, in Power Query right click on table name and choose duplicate.
From now on working on duplicated query:
Go on home tabe and choose group by. Group by column Order Number and add 3 aggregations:
1) Items count - how many records per Order Number
2) Items = 0 - returns min value for Value. If there is value with 0 it will return 0. Based on this column you will hide/remove these rows
3) Sum amount - sum of volume
My demo screenshots below:
Filter column Items = 0 (uncheck 0 from list).
Filter column Items count - (uncheck 1 from list to get only Order numbers with multiple items)
Click save and apply and you have a new list of orders.
It worked! I'm going to try the other solutions (dax included) but this solution worked!
Thanks man!