Check Column Values in a Table for a List of Characters in Another Table

[EllieKS]

Hi, I am trying to evaluate with calculated columns whether the values in a column with mixed data types contain any character listed in another table.

I have two tables, Table A contains a list of all characters I need to search for in the Data Column of Table B which has mixed data types. I have added two columns to Table B showing the desired result. Any help would be much appreciated.!

Table A		Table B
Characters		Data Column	Desired Result - True/False	Desired Result - Character
"		Name@email.com	FALSE
#		2016-05-27T11:00:00	TRUE	:
%		8964df00-dba8-4834-8964-75323a35df63	FALSE
&		4000	FALSE
*		500.22	FALSE
:		n/a	TRUE	/
<		*Check Reference	TRUE	*
>		>200	TRUE	>
?		Text 123	FALSE
\		Text	FALSE
{		123	FALSE
}		ABC & 123	TRUE	&
|
~
/

[ebeery · Accepted solution]

@EllieKS there are probably several possible solutions to this, but one way I've used before is using Power Query.

Here are a couple blog posts from which I adapted the code.

Searching for text strings in power query 
Create a list of matching words when searching text in power query 

After converting TableA in to a list (CharacterList), the code for Table B looked like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYvBCsIwEER/peQopm52kzR6Unv3IL3VHmKzQdFUEAU/XxursLAzjzdtK3Y+8ZqTP1/L/pZEN28FgrISjMSqUWoF8LnM3dLqEAFkOHontSMtRyQrQ0ieTIiWsqhhWhiAEjHHYeHzn9Un7i/FniPfeeg5w8MTgBinVcOvR6GQ/iWHH9hs62L00X6d7g0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data Column", type text}}),
    #"Added True/False" = Table.AddColumn(#"Changed Type", "Found?",
        (FindStrings) =>
        List.AnyTrue(List.Transform(CharacterList, each Text.Contains((FindStrings[Data Column]), _ )))),
    #"Added Found Character" = Table.AddColumn(#"Added True/False", "Found Character",
    each List.Accumulate
        (
            CharacterList,
            "",
            (state, current) =>
                if Text.Contains([Data Column], current, Comparer.OrdinalIgnoreCase)
                then state & " " & current
                else state

        ))
in
    #"Added Found Character"

See .pbix file linked below:

https://drive.google.com/file/d/1JPteKLcVGLz40eOTZkZpIR-Hnc2SyPBk/view?usp=sharing 

[mahoneypat · Accepted solution]

The solution by @ebeery is a good one. FYI that you can mark it as a solution even if you can't use an M solution. Here is a DAX measure that works too. You can switch between the return lines to get the two different values (T/F or which character(s) is found).

DAX Found =
VAR chars =
    DISTINCT ( TableA[Characters] )
VAR datavalue =
    MIN ( TableB[Data Column] )
VAR result =
    FILTER ( chars, FIND ( TableA[Characters], datavalue, 1, 0 ) ) 

// return countrows(result) > 0
RETURN
    CONCATENATEX ( result, TableA[Characters], "," )

Pat

[EllieKS]

That's perfect, thank you!

[ebeery · Accepted solution]

@EllieKS there are probably several possible solutions to this, but one way I've used before is using Power Query.

Here are a couple blog posts from which I adapted the code.

Searching for text strings in power query 
Create a list of matching words when searching text in power query 

After converting TableA in to a list (CharacterList), the code for Table B looked like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYvBCsIwEER/peQopm52kzR6Unv3IL3VHmKzQdFUEAU/XxursLAzjzdtK3Y+8ZqTP1/L/pZEN28FgrISjMSqUWoF8LnM3dLqEAFkOHontSMtRyQrQ0ieTIiWsqhhWhiAEjHHYeHzn9Un7i/FniPfeeg5w8MTgBinVcOvR6GQ/iWHH9hs62L00X6d7g0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data Column", type text}}),
    #"Added True/False" = Table.AddColumn(#"Changed Type", "Found?",
        (FindStrings) =>
        List.AnyTrue(List.Transform(CharacterList, each Text.Contains((FindStrings[Data Column]), _ )))),
    #"Added Found Character" = Table.AddColumn(#"Added True/False", "Found Character",
    each List.Accumulate
        (
            CharacterList,
            "",
            (state, current) =>
                if Text.Contains([Data Column], current, Comparer.OrdinalIgnoreCase)
                then state & " " & current
                else state

        ))
in
    #"Added Found Character"

See .pbix file linked below:

https://drive.google.com/file/d/1JPteKLcVGLz40eOTZkZpIR-Hnc2SyPBk/view?usp=sharing 

[EllieKS]

Hi @ebeery  Thank you very much for the solution, however, Table B is a virtual table with values that change constantly, hence looking for a DAX solution ideally. I am not sure how I can use your solution in this case?