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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors