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:
If you want to solve Excels XIRR-function with M in PowerBI or PowerQuery you have to use a goal seeking algorithm. I tried it with a binary-search and the results were quite good (on my scale):
Comparison of XIRR results between native Excel function and M
I knew that the performance of M in the query editor of PowerBI was much better than in Excel, but only today I discovered the incredible difference we actually have here:
If you want to apply the BOM-solution I’ve posted here, you’ll soon discover that the performance in Excel starts to suck with large datasets. Performance decreases exponentially and my sample datasets with 4 levels and 100k rows didn’t went through, 16 GB RAM constantly at the limit, unable to do any other task at the same time.
In contrast, performance in PowerBI totally blew me away: Memory management is different. Rise in RAM-consumption was always below 3 GB, even with my largest dataset (a 5-level 1Mio (!) rows BOM table that exploded to 3,8 Mio rows). Also no sweat in CPU, so I was able to easily perform other tasks at the same time on my laptop.
After the great announcement yesterday that we will be able to ship custom functions within the shared environment, I’m expecting a lot of people starting to write awesome custom functions for M. Hopefully they will all have nice function descriptions/metadata shipped with them, that makes it as easy as possible for users to apply them correctly:
Edit 12-Jan-18: Code and file updated (robustness & speed)
Handling multilevel bill of materials (BOM) without VBA in Excel and PowerBI is now a piece of cake: Just pass 4 parameters into the M-function below and it will return a table that holds everything you need for:
Order list (“total quantities”)
3. Implosion (“where used”): Will be covered in next blogpost
The format of the input-data for this function needs to be like the example used from the Adventure Works 2008-database, where all products on the top of the hierarchy also have an entry in the child-column (the components), leaving the parent column blank: