Filter table based on value conditions

[Anonymous]

Hello,

I have the following table loaded in Power Query and i am trying to filter out all the rows in the table based on the "Remaining" column if the value is zero, except if there is a non-zero value for the same "Code" number. Is this possible? Any help is much appreciated!

Code	Value	Date	Remaining
3998	1478	08/06/2021	0
3998	325	12/04/2022	325
3998	172	18/06/2022	172
1542	1980	13/06/2022	0
4253	2981	11/07/2023	2981
4943	1781	02/09/2022	1781
3991	4510	08/06/2021	4510
4060	6467	05/07/2022	0
4139	6570	05/07/2022	6570
4145	4437	08/06/2021	0
4149	6554	05/07/2022	6554
4150	6408	05/07/2022	0
4150	7049	08/06/2021	7049
4151	6411	05/07/2022	6411
4152	4413	05/07/2022	4413
4166	4372	05/07/2022	4372
4166	6729	05/07/2022	6729
4169	6770	05/07/2022	0
4169	6548	05/07/2022	0

Expected results:

Code	Value	Date	Remaining
3998	1478	08/06/2021	0
3998	325	12/04/2022	325
3998	172	18/06/2022	172
4253	2981	11/07/2023	2981
4943	1781	02/09/2022	1781
3991	4510	08/06/2021	4510
4139	6570	05/07/2022	6570
4149	6554	05/07/2022	6554
4150	6408	05/07/2022	0
4150	7049	08/06/2021	7049
4151	6411	05/07/2022	6411
4152	4413	05/07/2022	4413
4166	4372	05/07/2022	4372
4166	6729	05/07/2022	6729

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHZDcQwCER7yXeQOE1cS7T9t7EBH5trvyyGp2HA+75IrduyLqQeDyMTYAGMApfPOgFha30GVCDu0okg50kUoCEFQabRo7rhGZE5RNmi4LpRAwTQgWhoyVSV9BzMYVIBeWg9SjTVCO/bpJY+WKJZtPj0cUD7hSGpAZjjHUitMRq1qvj72Q6gmZg+TQ6tMdaS4PYvSQKO6XWZklpnKE2IHoNC6wxn2jz5hUmtMaVELadvHIz0f+xMca6PWaF1Jhf35/XwApi+bf35Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Value = _t, Date = _t, Remaining = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Value", Int64.Type}, {"Date", type date}, {"Remaining", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"RemainingCount", each List.Count(List.Select([Remaining], each _ <> 0)), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Code"}, #"Grouped Rows", {"Code"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"RemainingCount"}, {"RemainingCount"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Grouped Rows", each ([RemainingCount] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RemainingCount"})
in
    #"Removed Columns"

[Syndicate_Admin]

This, indeed, is the perfect solution and the code is reusable for similar problems. It would be nice if the solution can be replicated using only the GI (conditional columns and the groupby options), for simplicity.