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"
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"