Forum Discussion
Power Query Apply takes too long, eventually locks up PC
Power BI's performance when Applying changes is utterly frustrating me and killing my productivity.
Last night it spent 4+ hours evaluating and loading data sources, eventually when I returned to my PC this morning it was switched off! Something so bad had happened that the PC didn't just restart, it shut down!
Today I tried again and everything locked up, so I hit the hard restart button.
- PC memory is 32Gb, sufficient for everything else I do (video editing etc)
- It's not a huge report, the .pbix file is 350Mb
- There are about 20 data sources, all flat files (no database connections etc)
- The data sources vary in size, there is a large 580Mb one, but the rest are 150Mb down to 30Mb, files, and even some 5-10Mb files, to me this isn't a huge amount of data
- There is an outer join where a 580Mb file is joined to a 55Mb file, maybe just maybe the outer join is a killer but this is an essentially part of my analysis
- I'm only "loading" about 6 reports, and the changes I'm making to a file are affecting a total of 4 loaded reports
This thing just sits and does it's thing, until it doesn't and I have to restart:
I dread clicking the Apply button, and I've learned that I have to Apply in the smallest increments possible, i.e. I can't make huge wholesales changes as that will completely lock everything up, so I have a habit of very small changes and Applying, but even now this is adding a single file into the mix and causing today's issue.
I know there's plenty of jokes about the dreaded dots and waiting for the Previews to refresh, and yes I feel this pain but I can handle waiting a few mins for this:
But I must be missing something around performance / efficiencies / cache / some sort of settings that help this Apply run faster because it is not working well.
What am I missing? Where can I go for help?
1 Reply
- ppm1Solution Sage
Please share your M code form the Advanced Editor. You are likely doing memory intensive operations like merge, sort, etc. and/or reading in your files multiple times. The community can likely suggest some optimizations.
Pat