Is it Possible to Find a Match between a Column in a Table and a Cell in Another Table?

[Anonymous]

Hello,

I'm trying to use Power Query to Perform a Look up or a search between the values in a column from a Table to the values in cells from another Table.

For Example. 

Here is table 1.  It list the states I need to ID in cell values from table 2.

STATE

NC

VA

SC

GA

FL

Here is table 2.  It shows a pathway of States traveled.  I am trying to find a way to output if any STATE in Table 1 is within any cell value/Row in column STATES TRAVELED in Table 2 then  Output "YES"

EMPLOYEE	STATES TRAVELED	(Output)
John	AK, WA, NV	No
Paul	AK, NC, NV, GA	Yes
Sam	NC, NC, FL, VA	Yes
Amy	SC, NC, FL	Yes
Kate	TX, AZ, NM	No

Is this possible?  

THANK YOU FOR YOUR HELP!

[HotChilli · Accepted solution]

As a general rule, powerbi prefers columnar data rather than, for example, comma-separated lists within a field.

Anyway, here goes

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXL01lEId9RR8AtTitWJVgpILM2Bivo5g0R1FNwdwTLBiblACbAgELv56CiEQSQccyuBEsFwCbCgd2JJKlA0JEJHwTEKKOOrFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [EMPLOYEE = _t, #"STATES TRAVELED" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPLOYEE", type text}, {"STATES TRAVELED", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "STATES TRAVELED", "STATES TRAVELED - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"STATES TRAVELED", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "STATES TRAVELED"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"STATES TRAVELED", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"STATES TRAVELED", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if (List.Contains(TableStates[STATE], [STATES TRAVELED] )) then "Yes" else "No"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"EMPLOYEE", "STATES TRAVELED - Copy"}, {{"Visited Any", each List.Max([Custom]), type text}})
in
    #"Grouped Rows"