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.
Community ChampionSee 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.