Edited to elaborate on solution needed and expected results
I have to match up customers from one system with another system. One system has First Name, Lasst Name, ID and date of service.
The other system has a combined Laast name,First name which includes Juniors, IIIs and hyphenated names. it also has the same date of service and a different account number.
I want to get the id from the second system combined with the data from the first system. I think that the first name and the last name from the first system are included in the combined last name,first name in the second system. I don't want to do this using Fuzzy Joins, I would like to do this with either power query or dax measures. Below is a link to my fake data. In excel I can do this by a formula that matches up rows containg the partial string.
The solution I want, but don't know how to achieve is this: for each row in system 1 find the row in system 2 where the name column includes the first name from system 1 and the name column in system 2 also contains the last name from system 1 and the dates match. Then the account number from the matching row in system 2 will be added to the row that it matched in system 1.
For example Baker Jr, Mike in system 2 would not match a combined helper column in system 1 because system 1 does not have the "Jr", but if you can recognize that the Mike in system 1 is included in the Baker Jr, Mike, and the Baker from system 2 is also included in the Baker Jr, Mike and the Date of 6/30/25 in system 1 matches the date in system 2, therefore the 1212 would be matched up with the row in system 1.
https://www.dropbox.com/scl/fi/3as9vqqeh9ufm9524vagy/Test-1.xlsx?rlkey=140jxgdryxq3ovjm7lqxhhqpx&st=...
