Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merge Queries by text contain

Hello everyone, I refer to this query https://community.powerbi.com/t5/Power-Query/Merge-by-Text-Contain-Any-Multiple-values-in-a-row/td-p/745087 as I have a similar issue. (Also Nolock , since you p...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Ooh I see. Is there any particular example that I can refer to?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

here an exemple of use of List.transformMany and a very basic Fuzzy compare function:

 

let
ruleList=Table.ToRecords(ruleTab),
tab=Table.FromRecords(List.TransformMany(dataTab[Titles], each ruleList,(x,y)=> if MyFuzzy(x,y[Titles]) then [Titles=x,Function=y[Function]] else [Titles=x,Function=null])),
    #"Rimossi duplicati" = Table.Distinct(tab, {"Titles","Function"}),
    #"Raggruppate righe" = Table.Group(#"Rimossi duplicati", {"Titles"}, {{"Func", each _[Function]}}),
    #"Valori estratti" = Table.TransformColumns(#"Raggruppate righe", {"Func", each Text.Combine(List.Transform(_, Text.From)), type text})
   in
    #"Valori estratti"

 

 

MyFuzzy:

 

let

myFuzzy=(fieldData,fieldRules)=> 

let
listRules=Text.SplitAny(Text.Lower(fieldRules)," -,"),

listData=Text.SplitAny(Text.Lower(fieldData)," -"),

fuzzyMatch=List.ContainsAny(listRules,listData)


in 
fuzzyMatch
in
myFuzzy