Performance Tip: Partition your tables at crossjoins where possible – PowerQuery PowerBI

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 🙂

Comments (4) Write a comment

  1. Hi Imke

    I love your blog, keep up the good work!

    One request, if possible, are you able to put in your code snippets as text instead of pictures? It makes it easier to copy/paste 🙂

    Thanks,
    Simon

    Reply

    • Thanks Simon!
      I understand your request, but this conflicts a bit with highlighting and formatting. Lately I’ve started to enclose txt-files to download with the pure code in it.
      Here I thought that opening the link to the forum-post was also acceptable – will keep this in mind though 😉

      Reply

  2. Pingback: Tabellen zusammenführen mit relativer Bedingung - PowerBI Pro

  3. Pingback: #Excel Super Links #53 – shared by David Hager | Excel For You

Leave a Reply