Forum Discussion
Solved
Table not contains
Hello everyone, How can I get the result in the image below? I actually have a table with several columns and around 2000 rows. The problem here is that I'm looking for a word in a group of words ...
You implied you wanted to hard-code the excludes within the M-Code itself. So perhaps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDoIwEEWvMum6lxA9gHFjDGFRywCVOkOmJYbb2xYMxpXbn/cmL1PX6qC0uqH3/ALuIMykwRoBRyDYaqi8sSN0LECuH6Jq9KqcHY1g2bNoWFbdBeiyThyzftl1Jr8U0yTzOriIMJkJBQy1GzIZigNKoapE9YKYbrEY6hEes7NY6LSvp+w/0PGn9GmWO269OhcChIExfEJT9hOpqKevv0hGy9qmNWt7eIiLR9U0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Color = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", type text}, {"Color", type text}}), #"Select by Criteria" = Table.SelectRows( #"Changed Type", each not List.Contains({"A","D"},[Ref],Comparer.OrdinalIgnoreCase) and not List.ContainsAny(Splitter.SplitTextByAnyDelimiter({" ",","}) ([Color]),{"pink","yellow"},Comparer.OrdinalIgnoreCase) ) in #"Select by Criteria"
It is trivial to modify my routine if you want to use a Table as a data source for the filter.
Assume we have a table named "Filter" that looks like:
Two small changes in the M-Code to reference the Filter table and relevant column:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDoIwEEWvMum6lxA9gHFjDGFRywCVOkOmJYbb2xYMxpXbn/cmL1PX6qC0uqH3/ALuIMykwRoBRyDYaqi8sSN0LECuH6Jq9KqcHY1g2bNoWFbdBeiyThyzftl1Jr8U0yTzOriIMJkJBQy1GzIZigNKoapE9YKYbrEY6hEes7NY6LSvp+w/0PGn9GmWO269OhcChIExfEJT9hOpqKevv0hGy9qmNWt7eIiLR9U0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Color = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", type text}, {"Color", type text}}),
#"Select by Criteria" = Table.SelectRows(
#"Changed Type",
each not List.Contains(Filter[Ref],[Ref],Comparer.OrdinalIgnoreCase)
and not List.ContainsAny(Splitter.SplitTextByAnyDelimiter({" ",","})
([Color]),Filter[Color],Comparer.OrdinalIgnoreCase)
)
in
#"Select by Criteria"