Forum Discussion
Merge Queries by text contain
- Anonymous6 years ago
Thank you very much everyone for your input!
I found a simple solution here https://powerpivotpro.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/ and it worked for me perfectly.
Cheers!
It'd be great if anyone can help me regarding this! Thank you.
Remember that the table join functions support using other functions as joiner operators. You can do much (MUCH) more than just fuzzy matches. Conditional joins with complex logic over multiple fields - no problem. Imke Feldmann covered that, I can provide examples too.
- Anonymous6 years agoNot applicable
Ooh I see. Is there any particular example that I can refer to?
- Anonymous6 years agoNot applicable
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 - lbendlin6 years agoSuper User
Here's my proudest work so far:
#"Added Custom" = Table.AddColumn(Index, "Match", (k) => Table.SelectRows(#"Assignments", each ([LocID]="*" or k[LocID]=[LocID]) and ([PSA]="*" or k[PSA]=[PSA]) and ([ST ID]="*" or k[ST ID]=[ST ID]) ) ),This does a semi-fuzzy match across three fields of the two tables.