Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 sum...
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    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

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

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

hale's avatar
hale
Helper II
3 years ago

oh, awesome, didnt know you can do all rows in the group by. Learn a new thing today, thanks so much