Answered

Using fuzzy or phonetic look ups/filters

(0) Share

Report

Posted on by iskguy

402

I am working on an app that looks up persons by possible name matching. We have an outside service that supplies us a list of names in a csv file. We need to relate any possible matches to our list of consumers (clients) and one way is to match on the name. Last name is pretty solid (there might be a typo from the incoming file, but for the most-part we are good there). However, with first name it can be totally different. If we have a Stephen Jones in our master consumer file, but the incoming potential match is Steve Jones or even Steven Jones, this will not be a like-to-like match. I know that SSIS has something called "fuzzy" matching. How might this be done in power apps? I was thinking of connecting to an external service or web site that might have like names, but cannot find any legit ones. Or is there any DAX or something in a power query that can do this and give the name a possible match score, or weight? And then we would only match based on that weight?

Thanks for any direction and advice. Have a great day!

Categories:

General Questions

All responses (4)

Answers (1)

MV-30041934-0 97 on at

Like (0)

Report

Re: Using fuzzy or phonetic look ups/filters

I'm curious if this can be applied to extremely large datasets. Are there other ways to do fuzzy matching with dataverse sources, for example?
BamSham 4 on at

Like (0)

Report

Re: Using fuzzy or phonetic look ups/filters

Adding Aliases sorted it, the replacement for Result of Value confuses itself on the for all loops so needs the aliases but works as below
Filter(PersonList,With(
{SplitTarget:Split(TextInput1.Text," "),TargetLength:CountRows(Split(TextInput1.Text," "))},
Sum(ForAll(
SplitTarget As ST,
Sum(ForAll(Sequence(CountRows(Split(ST.Value,""))) As SQ,If(Concat(FirstN(Split(SQ.Value,""),SQ.Value),ST.Value) in messagetext,{Count:1.0/CountRows(Split(SQ.Value,""))})),Count)*1.0/TargetLength),Value))>0.6)
BamSham 4 on at

Like (0)

Report

Re: Using fuzzy or phonetic look ups/filters

Loving this solution, however cant get to work now that Result is not recognised?? can update to Value, however the resulting figures then do not match yours, not sure if microsoft have updated their functions, does this code still work for you?
Verified answer

v-bofeng-msft on at

Like (3)

Report
Re: Using fuzzy or phonetic look ups/filters

Hi @iskguy ,

Fuzzy matching is a relatively complex algorithm, currently PowerApps does not provide Fuzzy matching function out of box. To this end, I designed a simple fuzzy matching algorithm for your reference:

I assume that I want to calculate the similarity of two string type variables 'InputText' and 'TargetText'

Set(InputText,"Stephen Jones");Set(TargetText,"Steven Jones");

And then you can get the percentage by this formula:

With( {SplitTarget:Split(InputText," "),TargetLength:CountRows(Split(InputText," "))}, Sum(ForAll( SplitTarget, Sum(ForAll(Sequence(CountRows(Split(Result,""))),If(Concat(FirstN(Split(Result,""),Value),Result) in TargetText,{Count:1.0/CountRows(Split(Result,""))})),Count)*1.0/TargetLength),Value))

Now, we can apply this algorithm to a real case:

1\I assume there is a person list:

ClearCollect( PersonList, {PersonName:"Stephen Jones"}, {PersonName:"Steven Jones"}, {PersonName:"Steve Jones"}, {PersonName:"Bruce Lee"}, {PersonName:"Doctor Strange"} )

2\Add a text input control(TextInput1.Text) and input "Stephen Jones"

3\You could search the person list by

Filter(PersonList,With( {SplitTarget:Split(TextInput1.Text," "),TargetLength:CountRows(Split(TextInput1.Text," "))}, Sum(ForAll( SplitTarget, Sum(ForAll(Sequence(CountRows(Split(Result,""))),If(Concat(FirstN(Split(Result,""),Value),Result) in PersonName,{Count:1.0/CountRows(Split(Result,""))})),Count)*1.0/TargetLength),Value))>0.6)

You can get all records with name similarity above 60%

Best Regards,

Bof