Groupby with condition

[Anonymous]

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]

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])}})

[nandic · Accepted solution]

@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]

It worked! I'm going to try the other solutions (dax included) but this solution worked!

Thanks man!

[edhans]

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.