Recently I’ve distributed some techniques for partial matches or relative joins between tables using PowerQuery or the query editor in PowerBI. They are very flexible and powerful – yet slow.
To improve performance you can check if there is a chance to “partition” your table using a Table.Group. If you have an equality expression in your statement like we had in our rolling-12-months-exercise here for example:
You can boost performance into a different dimension by grouping your table on the “Associate”-table instead like this:
The performance difference is huge, so thx to Colin Banfield for pointing this out (if you’re interested in M-code, you’ll also find an awesome alternative technique from Bill Szysz in that post). But have in mind that the performance also depends on your source data, so the performance gains my vary.
Also check out this post by Chris Webb which holds some more background information about this topic.
Enjoy & stay queryious 🙂