Advanced filtering in power query - multiple conditions

[MaxMaxMax]

Dear community member,

I am struggling with filtering and using multiple conditions within a few columns. I think that Table.SelectRows should solv it somehow, but not sure. I want to keep rows in my dataset that match the following conditions. 

Column CDN is ABC123 values, could be duplicated (more than one in the list)

Column P values can be any number starting from 1 up to 999;

Column T values can be only "A00" or "B00" or "1PL";

Columns TDV and TD are numeric values

The list itself may have from 1 up to ~10 000 lines/rows

Conditions:

1) if there is a certain row where Column CDN = "X" and Column T = "1PL" and Column P = "Y" , than keep this row;

OR

2) if there is a certain row where Column CDN = "X" and Column T = "A00" and Column P = "Y" , than keep this row;

AND

3) if there is a certain row where Column CDN = "X" and Column T = "A00" and Column P = "Y", than check all list from begining & delete/remove row where 

               Column CDN = "X" and Column T = "B00" and Column P = "Y" if so

OR

4) if there is NO certain row where Column CDN = "X" and Column T = "A00" and Column P = "Y", than check all list & keep row where 

               Column CDN = "X" and Column T = "B00" and Column P = "Y" if so

On the picture below you can see in red lines that should be removed/hide after filtering , e.g. there should be only non-red lines. 

Thank you all in advance

Cheers!

Alex

[Anonymous]

Hi @MaxMaxMax ,

Your four filtering conditions don't seem to cover all the highlighted records in the sample table you provided. Could you please provide more specific filtering criteria based on this sample table?

Best Regards,

Bof

[MaxMaxMax]

Hi @Bof,

Basically, the rows which may be deleted are related to those were values B00 in column T.

To get the conditions easier: 

1) all lines where "A00" either "1PL" in columns T - should remain

2) for lines where B00 in column T , it depends: 

 2.1. If there is an another row where columns CDN and P have identical values to our current line - than delete line with B00 and leave line with A00

 2.2 if there is NO such row where CDN and P columns values are identical to our line - than keep this one

see examples: 

B00 should be deleted , since there is already another row with A00 and the same values in first two columns; 

Lines  1 and 4  B00 should remain (those are only lines for values in CDN and P together), however Line 3 B00 should be deleted (because there is already a line with A00 and identical values in first two columns)

3. All lines should remain because its either A00 or 1PL.

hope it helps

cheers!

[Anonymous · Accepted solution]

Hi @MaxMaxMax ,

Please try this m code formula:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNNboMwEIXvwjoaza89XqYRiEWDmqZVQVHuf41YQNVawSgLkBfv87w3M77dGtb2isiOJIjn/nTU5tBQ/o6I+c+MDs75ZBG0uR92iLeVCAHY8gkBN4Cfc4cFQIyQwiv6xRIpA82OgDccrXr+u18jiNfuf75eFIJW7YytSaFXdYeFzMFtH1kcaTAFrEYe/GuwskWSDGK1p4N/ly2N4gL4rCfDmPWYeuECMHKQuWYqItQBiTCn2jbUdp+lI0t5aqEe+R+wtNWi8bJ45GkbuVyoqCGBIWK1RN+dysmRSgCfSQTyfeR3l/KMU6zVGK9TLGNrnkN1mcZh8vLtmK279Jo+SNqTT6OvL4E+3lfVqr0/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CDN = _t, P = _t, T = _t, TDV = _t, TD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CDN", type text}, {"P", Int64.Type}, {"T", type text}, {"TDV", type number}, {"TD", type number}}),
    GroupedTable = Table.Group(#"Changed Type", {"CDN", "P"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    MergedTable = Table.NestedJoin(#"Changed Type", {"CDN", "P"}, GroupedTable, {"CDN", "P"}, "GroupedData", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "GroupedData", {"Count"}),
    FilterTable=Table.SelectRows(ExpandedTable,each ([T]="A00" or [T]="1PL") or ([T]="B00" and [Count]=1)),
    RemoveColumn=Table.RemoveColumns(FilterTable,"Count")
in
    RemoveColumn

 Before

After

Best Regards,

Bof

[MaxMaxMax]

@Anonymous 

Thank you! that works perfect!!

[Omid_Motamedise]

use the following comination of conditions

    FilteredRows = Table.SelectRows(Source, each 
        // Condition 1: Keep rows where CDN = "X" and T = "1PL" and P = "Y"
        ([CDN] = "X" and [T] = "1PL" and [P] = "Y")
        
        // Condition 2: OR keep rows where CDN = "X" and T = "A00" and P = "Y"
        or ([CDN] = "X" and [T] = "A00" and [P] = "Y")

        // Condition 3: OR remove rows where CDN = "X" and T = "B00" and P = "Y" 
        // only if a row with CDN = "X", T = "A00", and P = "Y" exists
        or (Row1Exists and not ([CDN] = "X" and [T] = "B00" and [P] = "Y"))

        // Condition 4: OR keep rows where CDN = "X" and T = "B00" and P = "Y" 
        // if no row with CDN = "X", T = "A00", and P = "Y" exists
        or (not Row1Exists and [CDN] = "X" and [T] = "B00" and [P] = "Y")
    )

[MaxMaxMax]

Hi @Omid_Motamedise ,

thank you for a code, however there is an error pop ups: 

"Expression.Error: The name 'Row1Exists' wasn't recognized. Make sure it's spelled correctly."

Also, how can I replace values X and Y to reflect actual data in the cell for a particular row?

Thank you

[AntrikshSharma]

@MaxMaxMax What is X for CDN here? Current row's value?

[MaxMaxMax]

Hi @AntrikshSharma 

variables such as "X" and "Y" are related to each current data list in cell. 

For example, 

CDN for both lines in the list = 24ES002801300MHCA4

P for both lines in the list = 1 

T = A00 , B00

Thank you