Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
C_mexico
New Member

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. Screenjpg.jpg

 Is this possible to do in Power Query? In case yes, then how? 

Thanks a lot in advance. 

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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

View solution in original post

3 REPLIES 3
jbwtp
Memorable Member
Memorable Member

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

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

hale
Helper II
Helper II

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 resultEnd result

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors