Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Advanced filtering in power query - multiple conditions

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 i...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    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

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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")
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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