Filter Column A based on another Filter on Column B

[shayla]

Hi All, 

I would like to filter Quote ID based on the item column.

I would like to have all the lines from a quote which has AAA or DDD in the quote. 

How could I achive this in Power Query?

Thanks,

[jennratten · Accepted solution]

@shayla - here you are.  The primary difference between this solution and the one posted by @bhanu_gautam is looking to match cells containing AAA and DDD instead of an exact match.  There must be some white space characters in the cells with the Item values.  

Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0dFRQitWBcJycnOBsZ2dnONvFxQXMNkJSY4SkxhhiEJwNEzdBtsAESbMpzNBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Quote ID" = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quote ID", Int64.Type}, {"Item", type text}}),
    QuoteIdsByItem = Table.SelectRows ( 
        #"Changed Type", (x) => List.AnyTrue(List.Transform({"AAA","DDD"}, each Text.Contains((x[Item]), _, Comparer.OrdinalIgnoreCase ))) ),
    Custom2 = Table.SelectRows ( #"Changed Type", each List.Contains ( QuoteIdsByItem[Quote ID], [Quote ID] ) )
in
    Custom2

[PavelAdam · Accepted solution]

@shayla want to get every record (row) of such [Quote ID] value, that contains [Item] value either "AAA" or "DDD".
Solution workflow: load data, clean Item text values (trim, clean), group by [Quote ID], filter to [Quote ID] which passes test (presence of "AAA" or "DDD" value), expand groups to a flat table.

Solution code:

let
/*https://community.fabric.microsoft.com/t5/Power-Query/Filter-Column-A-based-on-another-Filter-on-Column-B/td-p/4054856*/
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0dFRQitWBcJycnOBsZ2dnONvFxQXMNkJSY4SkxhhiEJwNEzdBtsAESbMpzNBYAA==", BinaryEncoding.Base64), Compression.Deflate)), type table [#"Quote ID" = Int64.Type, Item = Text.Type]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Quote ID", Int64.Type}}),
    CleanTextValues = Table.TransformColumns( ChangedType, {"Item", each Text.Clean(Text.Trim(_)), type text}),
    GroupedRows = Table.Group(CleanTextValues, {"Quote ID"}, {{"AllRows", each _, type table [Quote ID=nullable number, Item=text]}}),
    FilterGroups = Table.SelectRows(GroupedRows, each List.ContainsAny([AllRows][Item], {"AAA", "DDD"})),
    ExpandGroupToTable = Table.ExpandTableColumn(FilterGroups, "AllRows", {"Item"}, {"Item"})
in
    ExpandGroupToTable

[jennratten · Accepted solution]

@shayla - here you are.  The primary difference between this solution and the one posted by @bhanu_gautam is looking to match cells containing AAA and DDD instead of an exact match.  There must be some white space characters in the cells with the Item values.  

Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0dFRQitWBcJycnOBsZ2dnONvFxQXMNkJSY4SkxhhiEJwNEzdBtsAESbMpzNBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Quote ID" = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quote ID", Int64.Type}, {"Item", type text}}),
    QuoteIdsByItem = Table.SelectRows ( 
        #"Changed Type", (x) => List.AnyTrue(List.Transform({"AAA","DDD"}, each Text.Contains((x[Item]), _, Comparer.OrdinalIgnoreCase ))) ),
    Custom2 = Table.SelectRows ( #"Changed Type", each List.Contains ( QuoteIdsByItem[Quote ID], [Quote ID] ) )
in
    Custom2

[bhanu_gautam]

@shayla , either you can use m code for this or you can simply filter item rows which are equal to AAA or DDD

let

    Source = YourDataSource, // Replace with your actual data source

    FilteredItems = Table.SelectRows(Source, each ([item] = "AAA" or [item] = "DDD")),

    FilteredQuoteIDs = Table.SelectRows(Source, each List.Contains(FilteredItems[Quote ID], [Quote ID]))

in

    FilteredQuoteIDs

[shayla]

Hi @bhanu_gautam ,

It doens't seem working. could you please seperate the steps, as I am quite a newbie to power query.

Thanks,

[bhanu_gautam]

@shayla , Can you share your sample data