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:
- Access the full datamodel via Pivots
- Import the table via PowerQuery (See in the comment)
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 😉
imke, thanks for bringing this topic, PowerBI Desktop is much much faster than Excel.
I’ve also seen poor performance with millions of rows of transaction data. I’ll import the Excel model to Power BI and see if better.
Thanks for the votes and the blog post. Completely agree. This issue is killing me. It is made worse by the lack of DAX calculated tables in excel. The combination a real one two punch for data modeling.
Pingback: #Excel Super Links #36 – shared by David Hager | Excel For You
Pingback: Ken Puls Explains Why Excel's Power Query Refresh Speeds Suck
Thanks Imke, for this and all your other teachings – they are wonderful. I am also noticing a significant performance difference with very small datasets (5500 rows, <10 columns). Refresh all times Excel of 3-5 miinutes, PBI – 20-30 seconds for same set of queries (30 queries, of which 4-5 are somewhat complex). Also both the Excel and PBI connect to the same external (small) Excel tables. I’m looking into the causes, but it is quite troubling since this situation absolutely calls for an Excel front end. If you or any of your readers have similar experiences or ideas, I would love to hear about it.