Checking keywords from another table

[Parco]

Hi, 

I have one keyword table (table1)

ABC

DE

FG

HI

JK

and a table to be checked (table2)

ABC-3

aABCD

YHIK

FGGG

JKKJ

DEA_B

May I ask how to give out the following result for table2 while both table 1 and 2 will be updated for more elements.

2ABC-3	ABC
ABCD	ABC
YHIK	HI
FGGG	FG
JKKJ	JK
DEA_B	DE

Thank you.

[v-stephen-msft · Accepted solution]

Hi @Parco ,

Here's my solution.

1.Add a custom column to insert the table2.

2.Expand the column you want to match.

3.Add a custom to match values.

4.Filter out the true value to get the final result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Here's another possibility.

(row) => List.First(List.Select(table2[Keyword], each Text.Contains(row[Text], _)))

This takes the list of keywords (table2[Keyword[), selects the keywords that are contained in the text from the current row of table1, and then takes the first element of that list (if it exists).

Full sample query (referencing table2 with a column [Keyword]):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jVWitWJVkoEMl3ArEgPT28ww83d3R3M8PL29gIzXFwd453ALJBqVzeIdERklFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (row) => List.First(List.Select(table2[Keyword], each Text.Contains(row[Text], _))), type text)
in
    #"Added Custom"

[Vijay_A_Verma]

Let's assume that column is named Data in both tables. Then in a custom column in Table2, you can put following formula

= try Table1[Data]{List.PositionOf(List.Transform(Table1[Data],(x)=>Text.Contains([Data],x)),true)} otherwise null

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jVWitWJVkoEMl3ArEgPT28ww83d3R3M8PL29gIzXFwd452UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result", each try Table1[Data]{List.PositionOf(List.Transform(Table1[Data],(x)=>Text.Contains([Data],x)),true)} otherwise null)
in
    #"Added Custom"