Forum Discussion

Parco's avatar
Parco
Regular Visitor
4 years ago
Solved

Checking keywords from another table

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 o...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
AlexisOlson
Super User
4 years ago

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"