Forum Discussion
Fuzzy option, or Table.FuzzyNestedJoin, not available
Dera All,
In my version of MS Excel I done have the option to merge two tables using the build in Fuzzy library option, e.g. 'Table.FuzzyNestedJoin', they are not install unfortunetaly. Is there a work-around using M lanague or is that very complicated? I have done the same task in DAX, again in PowerBI and Excel's DataModel , but I woud like to test if this can alos be done in M Language.
See Snippet, I have removed any sensative data, as a reference to what I am meaning :
Fuzzy Lookup
Any advise welcome.
Chris
You could try adding a column that uses text functions on the current row value, and use them to compare to the other table's column values as as a list, and then wrap that in Table.FindText. Of course the more parameters you use, the slower this would be:
Getlength = Table.AddColumn(PriorStepOrTableName, "len", each Text.Length([Client])-2, type number),
Custom3 = Table.AddColumn(Getlength, "front", each Text.Start([Client], [len]), type text),
Custom4 = Table.AddColumn(Custom3, "back", each Text.End([Client], [len]), type text),
Custom5 = Table.AddColumn(Custom4, "Fuzzy", each if List.Contains(Mail[Client], [Client]) then Table.FindText(Mail, [Client]) else if List.Contains(Mail[Client], [front]) then Table.FindText(Mail, [front]) else if List.Contains(Mail[Client], [back]) then Table.FindText(Mail, [back]) else null)
in
Custom5Basically, you are adding a column for some (by no means comprehensive) text permutations. Her is if the all but the first or last two characters match, we bring in a match from the other table. I'm sure that @Imke or any of the other experts could do this without adding the columns first. But just showing you that you can specify a lot of ways you' d like to match, and then use Table.FindText to bring in the matching rows instead of Table.Join.
Again, this is not very elegant, and it's pretty late, but like I said, any of the pros on here can probably improve upon this code once they get my point. Hope any of this makes sense, but the kids are upstairs like it's noon, and it's 1:43 AM, better see ehat's going on up there!
--Nate
2 Replies
You could try adding a column that uses text functions on the current row value, and use them to compare to the other table's column values as as a list, and then wrap that in Table.FindText. Of course the more parameters you use, the slower this would be:
Getlength = Table.AddColumn(PriorStepOrTableName, "len", each Text.Length([Client])-2, type number),
Custom3 = Table.AddColumn(Getlength, "front", each Text.Start([Client], [len]), type text),
Custom4 = Table.AddColumn(Custom3, "back", each Text.End([Client], [len]), type text),
Custom5 = Table.AddColumn(Custom4, "Fuzzy", each if List.Contains(Mail[Client], [Client]) then Table.FindText(Mail, [Client]) else if List.Contains(Mail[Client], [front]) then Table.FindText(Mail, [front]) else if List.Contains(Mail[Client], [back]) then Table.FindText(Mail, [back]) else null)
in
Custom5Basically, you are adding a column for some (by no means comprehensive) text permutations. Her is if the all but the first or last two characters match, we bring in a match from the other table. I'm sure that @Imke or any of the other experts could do this without adding the columns first. But just showing you that you can specify a lot of ways you' d like to match, and then use Table.FindText to bring in the matching rows instead of Table.Join.
Again, this is not very elegant, and it's pretty late, but like I said, any of the pros on here can probably improve upon this code once they get my point. Hope any of this makes sense, but the kids are upstairs like it's noon, and it's 1:43 AM, better see ehat's going on up there!
--Nate
Hi Nate
Many thanks, I will give this a try and let you know. What I should I explained, values are not in a uniform format. For example -
Find This Value Amongst this feild AB123 XX789, AC777 & ER777, AB123 ZZ111 ˽ZZ111 FG999 FG999˽TH888 So in the sample the frist column, I am able to clean and trim these values but I then need to find that value amonsgt the correprading column. So I would be dealing commas, or and ampersand or odd characters along with the odd space added for a good measure.
Disclaimer: Nate, I having an awlful feeling I may have asked the forum this question once before, I will have look back and check, but apology in the meantime if am wasting your time here.
Chris