Fuzzy Reverse vlookup - any idea

[LarsKarsten]

Hi

I'm new to this forum, sorry, in case I don't meet the right wording. I tried to find similar threads here but without success. 

I have a big table of data I import quite frequently. In one field is a text, quite random and up to 200 characters. Within this text could be keywords of different length and different positions I want to use to categorize data. For this I have a kind of Lookup table which provides me the category for those keywords (theoretically, this could lead to more than one category). I tried vlookup in excel with joker and found fuzzy vlookup as well, but the structure is differnet. I will try to sketch it here after.

Source (ca 5000 lines)

12/21/2023;this is a long text with a KEYWORD;additional data;4
12/19/2023;this is a text containg a TEXTSTRING which is good to define category;5
...

lookup table (ca 500 lines)
keyword; category

KEYWORD;long
TEXTSTRING;short

5232355;small

...

Desired result of an import / manipulation:

12/21/2023;this is a long text with a KEYWORD;additional data;4;long
12/19/2023;this text with TEXTSTRING which is good to define category;5;short

..

Hope the idea is clear.

If just the first category is shown, would be fine with me.

Any idea or hint?

thank you in advance

Lars

[LarsKarsten]

That works great. Thank you so much!

[AlexisOlson · Accepted solution]

For each row of the Source table, you can filter the lookup table to only include rows where the text from the Source table contains the keyword in the lookup table. Then take the category column from the lookup table. This returns a result of all the category matches and you can take the first one.

let
  Source = Table.FromRows(
    {
      {#date(2023, 12, 21), "this is a long text with a KEYWORD", "additional data", 4}, 
      {#date(2023, 12, 19), "this is a text containg a TEXTSTRING which is good to define category", null, 5}}, 
    type table [Date = date, Text = text, Data = text, Number = number]
  ), 
  lookup = Table.FromRows(
    {{"KEYWORD", "long"}, {"TEXTSTRING", "short"}, {"5232355", "small"}}, 
    type table [keyword = text, category = text]
  ), 
  Result = Table.AddColumn(
    Source, 
    "lookup", 
    each List.First(
      Table.SelectRows(
        lookup,
        (row) => Text.Contains([Text], row[keyword])
      )[category]
    ), 
    type text
  )
in
  Result