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

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