Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
LarsKarsten
New Member

Fuzzy Reverse vlookup - any idea

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

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

2 REPLIES 2
LarsKarsten
New Member

That works great. Thank you so much!

 

AlexisOlson
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors