Forum Discussion
Filter based on sum from another column
Hi,
I need help with applying a filter based on another column. In the example below, I need that Power Query filter the "order number" not taking into account any value where the order number sums 0.
Is this possible to do in Power Query? In case yes, then how?
Thanks a lot in advance.
Hi hale,
I would remove the unnecsssray asnd potentlially costly join. You can get away with this just using all rows as one of the agruments on the Table.Gourp:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYX1DfSMDIyMQ08BAKVYHIWWEkNJFlgOr1TeByZlCZJyATGNUA40gMs5ApgmqHjOIjAtIO6oeC3QZE2TTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, OrderNumber = _t, Date = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"OrderNumber", Int64.Type}, {"Date", type date}, {"Amount", Int64.Type}}), GroupByList = Table.Group(#"Changed Type", {"OrderNumber"}, {{"TotalAmount", each List.Sum([Amount]), type nullable number}, {"Data", each _, Value.Type(#"Changed Type")}}), #"Filtered Rows" = Table.SelectRows(GroupByList, each ([TotalAmount] <> 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"TotalAmount"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Name", "OrderNumber", "Date", "Amount"}, {"Name", "OrderNumber.1", "Date", "Amount"}) in #"Expanded Data"Cheers,
John
3 Replies
Here is my attempt, not sure that's the best way to do it but have the wanted result anyway:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYX1DfSMDIyMQ08BAKVYHIWWEkNJFlgOr1TeByZlCZJyATGNUA40gMs5ApgmqHjOIjAtIO6oeC3QZE2TTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, OrderNumber = _t, Date = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"OrderNumber", Int64.Type}, {"Date", type date}, {"Amount", Int64.Type}}), GroupByList = Table.Group(#"Changed Type", {"OrderNumber"}, {{"TotalAmount", each List.Sum([Amount]), type nullable number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"OrderNumber"}, GroupByList, {"OrderNumber"}, "GroupByList", JoinKind.LeftOuter), #"Expanded Group By List" = Table.ExpandTableColumn(#"Merged Queries", "GroupByList", {"TotalAmount"}, {"TotalAmount"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Group By List", each ([TotalAmount] <> 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"TotalAmount"}) in #"Removed Columns"Please ignore the code up to the Changed Type line, they are only to re-create your data manually.
End resultHi hale,
I would remove the unnecsssray asnd potentlially costly join. You can get away with this just using all rows as one of the agruments on the Table.Gourp:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYX1DfSMDIyMQ08BAKVYHIWWEkNJFlgOr1TeByZlCZJyATGNUA40gMs5ApgmqHjOIjAtIO6oeC3QZE2TTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, OrderNumber = _t, Date = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"OrderNumber", Int64.Type}, {"Date", type date}, {"Amount", Int64.Type}}), GroupByList = Table.Group(#"Changed Type", {"OrderNumber"}, {{"TotalAmount", each List.Sum([Amount]), type nullable number}, {"Data", each _, Value.Type(#"Changed Type")}}), #"Filtered Rows" = Table.SelectRows(GroupByList, each ([TotalAmount] <> 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"TotalAmount"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Name", "OrderNumber", "Date", "Amount"}, {"Name", "OrderNumber.1", "Date", "Amount"}) in #"Expanded Data"Cheers,
John
oh, awesome, didnt know you can do all rows in the group by. Learn a new thing today, thanks so much
