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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous ,

 

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    Anonymous
    Not applicable
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Anonymous 

      Thank you! that works perfect!!