• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power BI match keyword to table

Nightlytic

Member
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@

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

Thanks!
 

Attachments

  • Example.zip
    37.8 KB · Views: 4
You can adapt following custom function.

Code:
(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
Code:
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].
 
Oh, one more thing.

If you want to ignore case, I'd recommend using lower case/ upper case for both columns.
 
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:

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

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