Use table rows as a multiple filter criteria

[truewhiplash]

Hello M Experts, 

I hope someone could help me to resolve the following issue.

I have a table, where I would like to exclude rows (or flag for exclusion) based on the criteria defined in another table. Different combination of columns can be used as criteria. The original table, table with criteria and the output table look like this:

By looking at the community posts, I have found a function from @ImkeF (posted here: Merging queries based on multiple conditions ), however it uses each of the values as a separate filter, and then does an Outer Join on the tables. Can someone help to transform the function to consider multiple criteria, as well as wildcards/patterns?

It is important to resolve this via M Query, and not DAX Calculated Table, as I'm planning to use the table in other queries.

[Anonymous]

Waiting for your exhaustive explanations, I can try to guess the expected result.

This is an attempt

Filter table

Filtered table:

[Anonymous]

second attempt (which generalizes the control function to all filter tables.
There is no need to create it from time to time)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WylPSUcozBBKJQJxsqBSrE62UBmSmgcSSQGJGCDEjEB9NzBhIpIDEjMFipUBmKUgvSDLZBCEG0pvkWAISNUUTTQSLminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Company = _t, User = _t, Case = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Company", type text}, {"User", type text}, {"Case", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "to be filtered out", each Table.Contains(Filter, _, (x,y)=> if   
        (y[Market]=x[Market] or x[Market]="any")  and
        (y[Company]=x[Company] or x[Company]="any") and
        (y[User]=x[User] or x[User]="any") and
        (y[Case]=x[Case] or x[Case]="any") 
        then true else false))
in
    #"Added Custom"

To take a further step towards a more extensive generalization, we can replace strict equality with a function that takes into account wildcards and patterns

[truewhiplash]

Thank you, @Anonymous 

Your solution is close to what I'm looking for, however I would like to provide some examples on the usage of wildcards/patterns, which would allow to ignore certain filter values:

Original Table

Market	Company	User	Case
Nigeria	NG11	USER A	Case 1
France	FR10	USER B	Case 2
France	FR12	USER C	Case 2
France	FR10	USER D	Case 3
USA	US15	USER F	Case 4
Ghana	GH06	User G	Case 1
Georgia	GE20	User H	Case 10
Georgia	GE20	User I	Case 10

Filter Criteria

Market	Company	User	Case	Comment
Nigeria	*	*	*	All values where market = "Nigeria"
France	*	*	Case 2	All values where market is "France" and Case is "Case 2"
*	*	USER F	*	All values where User is "USER F"
*	*	*A*	*	All values where User contains "A"
*	G*	*	Case 1	All values where Company starts with "G" and Case is "Case 1"

Output

Market	Company	User	Case	Exclude
Nigeria	NG11	USER A	Case 1	Yes
France	FR10	USER B	Case 2	Yes
France	FR12	USER C	Case 2	Yes
France	FR10	USER D	Case 3
USA	US15	USER F	Case 4	Yes
Ghana	GH06	User G	Case 1	Yes
Georgia	GE20	User H	Case 10
Georgia	GE20	User I	Case 10

[Anonymous]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stMTy3KTFTSUfJzNzQEUqHBrkEKjkCGc2JxqoKhUqxOtJJbUWJecipQzC3I0ACmxgmmxghDjRFMjTNuNXBzXGBqjMFqQoMdwTKGpjAFbjAFJmAF7hmJeSD3unsYmIGUFKcWKbijutc9Nb8oHewpd1cjA5giD7giAzyqPJFVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Company = _t, User = _t, Case = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Company", type text}, {"User", type text}, {"Case", type text}}),
    match=(tab, filter)=>
    let 
    res= if filter="*" or tab=filter then true else 
    
    let 
    betStars= Text.BetweenDelimiters(filter,"*","*"),
    partOf= if Text.Length(betStars)>0 then  Text.Contains(tab,betStars) else false,
    startWith= if (Text.Start(filter,1)<>"*" and Text.End(filter,1)="*") then Text.StartsWith(tab, Text.BeforeDelimiter(filter,"*")) else false
    in partOf or startWith

    in res,  

    #"Added Custom" = Table.AddColumn(#"Changed Type", "to be filtered out", each Table.Contains(Filter, _, (x,y)=>    
        ( match(y[Market],x[Market])) and
        ( match(y[Company],x[Company])) and
        ( match(y[User],x[User])) and
        ( match(y[Case],x[Case])) 
        ))
in
    #"Added Custom"

[jmillar99]

How would I modify this M if one of my filter columns is a number?  When I apply this M I get several expression errors on the "to be filtered" column. "We cannot convert the Value 15 to type text."

[Anonymous · Accepted solution]

a more compact version, in case filter and tabel to be filtered have teh same fields:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stMTy3KTFTSUfJzNzQEUqHBrkEKjkCGc2JxqoKhUqxOtJJbUWJecipQzC3I0ACmxgmmxghDjRFMjTNuNXBzXGBqjMFqQoMdwTKGpjAFbjAFJmAF7hmJeSD3unsYmIGUFKcWKbijutc9Nb8oHewpd1cjA5giD7giAzyqPJFVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Company = _t, User = _t, Case = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Company", type text}, {"User", type text}, {"Case", type text}}),
    match=(tab, filter)=>
    let 
    res=  filter="*" or tab=filter or (  
    let 
    betStars= Text.BetweenDelimiters(filter,"*","*"),
    partOf= if Text.Length(betStars)>0 then  Text.Contains(tab,betStars) else false,
    startWith= if (Text.Start(filter,1)<>"*" and Text.End(filter,1)="*") then Text.StartsWith(tab, Text.BeforeDelimiter(filter,"*")) else false
    in partOf or startWith
    )
    in res,  

    #"Added Custom" = Table.AddColumn(#"Changed Type", "to be filtered out", each Table.Contains(Filter, _, (x,y)=> 
    List.Accumulate(Record.FieldNames(x),true, (s,c)=> s and match(Record.Field(y,c),Record.Field(x,c))) ))   

in
    #"Added Custom"

[Anonymous]

I could try to do some think close to what you ask.

But ... I need some tables (in a copiable format, may be a linked excel file) with as many cases as possible you want to manage.

explain better what you mean by matching pattern and wild card