Forum Discussion
Groupby with condition
Hello. My data has 2 fields: Order_Num and Volume.
I have repeated Order_num when a customer bought more than 1 item and each volume's entry correspond to the volume calculated with the itens' dimensions. If I don't have the item' dimensions, I replaced null for 0.
Example:
Order_Num | Volume
700500 0
700500 200
700500 300
700501 20
700501 50
So, I want to groupby the order_num of each order into a single entry and do a count to know how many orders are multi order or not but I want to exclude from my analysis all orders that have at least one entry with volume=0. In this example, 700500 will be excluded from my analysis because it has a volume=0 entry, and I want to merge 700501 two entries into one and sum volumes (vol=70).
How can I do it? Can me in Power Query M or DAX.
Thanks.
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.
4 Replies
- nandic
Resident Rockstar
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. - edhans
Community Champion
See this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcwMDUwUNJRMlCK1UHiGhmgCRgjCxiCVaDyTYH8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order_Num = _t, Volume = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Volume", Int64.Type}}), #"Grouped Rows" = Table.Group( #"Changed Type", {"Order_Num"}, { {"Total Volume", each List.Sum([Volume]), type nullable number}, {"Has Zero", each List.Contains([Volume], 0), type logical} } ), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Has Zero] = false)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Order_Num", "Total Volume"}) in #"Removed Other Columns"It groups your data as specified, but also includes a column if there is a zero in the transaction record.
Then I just filtered out the TRUE values and removed the Has Zero column, ending with this:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lEyUIrVgTKNkNjGULYhSBzBNFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ord = _t, vol = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ord", Int64.Type}, {"vol", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ord"}, {{"Count", each Table.RowCount(_), type number}, {"sum", each List.Sum(List.RemoveMatchingItems([vol],{0},(x)=>x*List.Product([vol])))}}) in #"Grouped Rows"Table.Group(#"Changed Type", {"ord"}, {{"Count", each Table.RowCount(_), type number}, {"sum", each List.Sum([vol])*List.Product([vol])/List.Product([vol])}})
