Forum Discussion

jasonnaylor's avatar
jasonnaylor
Frequent Visitor
3 years ago

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

  • ppm1's avatar
    ppm1
    Solution 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