Forum Discussion
Use table rows as a multiple filter criteria
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...
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"
Waiting for your exhaustive explanations, I can try to guess the expected result.
This is an attempt
Filter table
Filtered table:
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
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 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"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"