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.

     

     

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"