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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.