Power BI match keyword to table

[Nightlytic]

Hi all,

I need to get a fuzzy match going, the twist and issue is, I have power BI 2016 (work, can't update it!) which means the fancy fuzzy match feature is unavailable.
I'm not great at this... Can someone please help me out with sample?

I more or less want to look up a load of names from 'Data' to a reference table 'Reference' and bring back the resulting company/make
I don't need to account for typos, so for example:

Toyota Prius 2019 - contains keyword 'toyota' so 'toyota'
Used Toyota - contains keyword 'toyota' so 'toyota'
Tyota- ignore
Toy ota- ignore

Ideally no match will return a value like 'Other'
and multiple matches will just return one of them, be it top or bottom.

I found this online@

search text string in a column and see if it matches a value in a different tables column

I need help searcing searching a text string in a column to see if it matches a value in a different tables column. The tables are not connected. If the text string contains the value, I want to return another value into a new column. Sample tables: Table 1...

community.powerbi.com

But the file that solved it was deleted off filedropper :/

Thanks!

 

[Chihiro]

You can adapt following custom function.

(mystr as text, searchlist as list) =>
let
    Source = List.Generate(
                            ()=> [Word=null, x=-1],
                            each [x]<List.Count(searchlist),
                            each [Word = if Text.Contains(mystr, searchlist{x}) then searchlist{x} else null, x=[x]+1],
                            each [Word]),
    myWords = Text.Combine(List.RemoveNulls(Source),",")
in
    myWords

Since you only need first match. You can change last line before "in" to

myWords = List.First(List.RemoveNulls(Source), "Other")

This will return value from Reference[From]. You can then join Reference table based on this column.

Alternately, you can modify the custom function to add another argument for Reference[To].

 

[Chihiro]

Oh, one more thing.

If you want to ignore case, I'd recommend using lower case/ upper case for both columns.

 

[Nightlytic]

Hi Chihiro,

thanks, I copied the formula and that seems to work, but it seems my old BI doesn't have the invoke custom function option....

I guess I try insert it as a regular column? but this doesn't work:

 

[Chihiro]

When you invoke it... you can invoke it like...
= Functionname(Arg1, Arg2)

So in this case...
=SearchString([Name],Reference[Containts])

 

[Nightlytic]

You are the best!



Thank you thank you thank you!