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.
The 100k rows where Excel failed, went through in a good minute, 300k rows BOM exploded in 4 minutes to 1 Mio rows: For a recursive operation (using List.Generate instead of “real recursion”), this is very acceptable in my eyes. 1 Mio rows took a bit under 20 minutes to explode to 3,8 Mio rows.
I must say at I’m really impressed with the performance of PowerBI Desktop with M for a task like this on a PC!
Anyway – if you want to work with the results of your query in Excel (which isn’t so unusual for this kind of data), you have to rely on R for exporting it to csv or SQL-server or use some hacks:
Needless to say how much I hope that we wouldn’t need these workarounds. If you agree and want to take some action, please vote for a performance improvement of Power Query in Excel.
Enjoy & stay queryious 😉