Using Excel 2016 Power Query to Perform Conditional Inner Join

Question

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).

Answer 1

I would add a "Dummy Merge Key" column to both queries, e.g. = 1. Then I would start from the Transactions Query and add a Merge with the Vendors Query, matching on that "Dummy Merge Key" column. Then Expand all the Vendors columns.

At this point you have a massive "cross-join" of all Transactions rows crossed with all Vendors rows. Don't panic.

I would then add a Conditional column to check your criteria e.g.

Vendor Matched

= If Vendors.Name Contains VendorName then Yes Else No

Then Filter for Vendor Matched = Yes.

Performance is surprisingly good.