Reducing table size by filtering

Anonymous 

mahoneypat  has already provided a solution and full explanation. Here's another one in case it is easier to follow (although I am not sure it will on the first custom column). You'll have however to build some basic PQ knowledge on your own .It first creates a custom column with a list of the ID-Number values for the  Contract number in that row. Then it creates another custom column that checks if those values contain  1 and -1. Finally it filters out the Contracts with 1 and -1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzBDQAhCAR74Y3JoadgLYT+27hFuccQyO7gTtIHMUnCE7MDBZuCndQ29gdkZuC2M7tmk5L+N3bCd666teR1ehEf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract-number" = _t, #"ID-Number" = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract-number", Int64.Type}, {"ID-Number", Int64.Type}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "IDNumbers in contract", each Table.SelectRows(#"Changed Type", (inner)=>(inner)[#"Contract-number"] = [#"Contract-number"])[#"ID-Number"]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Has 1 and -1", each List.Contains([IDNumbers in contract],1) and List.Contains([IDNumbers in contract],-1)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([#"Has 1 and -1"] = false))
in
    #"Filtered Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers