Forum Discussion
Anonymous
6 years agoNot applicable
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...
- 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!
lbendlin
6 years agoSuper User
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.
Anonymous
6 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.