I have two tables in Excel 2016, a Vendor table, and a Transactions table. I'm trying to do something like this from SQL but in Power Query's Formal Language (M):
SELECT * FROM Transcations INNER JOIN Vendor ON Vendor.Name LIKE '%' + Transactions.VendorName + '%'
It's a simple query in SQL. But, merges in Excel seem not to allow conditional inner joins. Can anyone help with this, M, code?
let Source = Table.NestedJoin(Transactions,{"VendorName"},Vendors,{"Name"},"Vendors",JoinKind.Inner) in Source
Here's some sample data:
Vendors ------------------- Name, PeopleCount ------------------- ACME, 35 Microapple, 2000 Happyworx, 62 Transactions ------------------- TransactionID, VendorName, Amount ------------------- 1, Xaction: ACME, $19.50 2, Microapple 5/27 -- RYXTU, $32.75 3, Microapple 5/30 -- KJDIU, $7.23 4, Xaction: ACME, $22.32 5, Happyworx Store 7362, $3.23
These tables need to be joined on Transactions.VendorName LIKE '%' + Vendor.Name + '%' (if M has a wildcard delimiter).