0

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

  • Could you upload sample data? And for Join the Tables which key field you want to use? – Rajesh S Dec 31 '17 at 8:51
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.