PowerQuery Advanced Filtering Help

Hi Munk, Here is the PQ code to copy into excel, just add data blank query and paste below. You'll need to map the source to your source table in excel.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0synbLz08pVtJRMlWK1SFOJCQ/r9I5sQgkQBbfvzg5scgpMy8dbiKyCPlKnDOKMotLMvMSfRKL0lNh6jBEgYpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rate", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Null Rate", each if [Rate] = null then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Null Rate", Int64.Type}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Rate Value", each if [Rate] <> null then 1 else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Rate Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each Table.Group(#"Changed Type3", {"Name"}, {{"Null Rate Sum", each List.Sum([Null Rate]), type nullable number}, {"Value Rate Sum", each List.Sum([Rate Value]), type nullable number}})),
Custom = #"Added Custom"{0}[Custom],
#"Changed Type2" = Table.TransformColumnTypes(Custom,{{"Name", type text}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, #"Changed Type2", {"Name"}, "Changed Type2", JoinKind.LeftOuter),
#"Expanded Changed Type2" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type2", {"Null Rate Sum", "Value Rate Sum"}, {"Null Rate Sum", "Value Rate Sum"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Changed Type2", "Filter", each if [Null Rate Sum]>0 and [Value Rate Sum]>0 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Null Rate Sum", "Value Rate Sum"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Filter] = 1))
in
#"Filtered Rows"

Hi Anonymous ,

File here. 🙂 Let me know if all is ok. https://docs.google.com/spreadsheets/d/1PVQjiE6UnT5mWJioHenXtBmuHAKs_9MW/edit?usp=sharing&ouid=106154846235665801323&rtpof=true&sd=true