none
Extremely Slow Power Query.....only me RRS feed

  • Question

  • Good morning,

    There might very well be another thread on this but I'm new to this and under a bit of a crunch so please bear with me.

    I am struggling to get some of the queries that I built (and had help building; I'm pretty new to Power Query but it works amazingly for all of the linking and processing required in my job role) to run in any reasonable time.  I have a workbook with four large merge and table queries that pull data from multiple sources and several smaller queries that link in the data, mostly on Sharepoint locations and from our company server.  The data set is pretty large which is part of the reason it takes so long to run but there has to be something else.  When I run the smaller queries, it just takes a few seconds but when I run the larger ones, they take 30-90 minutes each and it can take an hour or two sometimes to just open the queries up.  It didn't used to take so long and it takes my manager 30-40 minutes to run all of the queries in the workbook (it takes me 4-6 hours).  It doesn't matter what computer I am on (to include my own login on my manager's computer), which made me think it had something to do with my user profile but no IT person in my enormous company seems to have any clue of what it takes to fix either the Power Query, my security settings, profile or any of the other issues that could be jamming me up.  I'm not sure where to even go from here as I'm running out of internal options.

    A little history:  I built this file along with several others about a year and a half ago and it's slowed down over time.  It finally REALLY slammed to a halt about 8 months ago and I found that my hard drive wasn't working correctly. I replaced it which didn't help, then defragged my computer, ran a hardware diagnostic (came up negative for issues) and even scrubbed my computer to the barest essential software and still no luck. 

    I don't know if anyone has had any similar issues but I could just use some direction to even start looking in.  I'm pretty well at the end of my knowledge and resources. 

    Thanks!

    Tuesday, July 11, 2017 6:26 PM

Answers

  • Hi Chuck,

    I have never seen a case where a different user profile makes such a difference and don't know much about the PQ internals. But I know that PQ also has algorithms who derive the execution plan from the actual M-code. So theoretically it could decide to go a different path if your rights to the underlying data is different to your managers. Very much hope that someone from Microsoft will pick this up here.

    Apart from that there is one default-setting that can kill performance: "Allow data preview to download in the background." - Have you turned this off already?

     Other than that, there is no real rule of thumb - apart from that sometimes buffering helps. Pls check out this post for it: https://blog.crossjoin.co.uk/2016/12/05/power-query-power-bi-and-the-allow-data-preview-to-download-in-the-background-option/

    When I'm faced with unbearable load-times, I create linkback or external staging tables to break the refresh-chain. But unfortunately there is still not tool for the users to check whats eating up performance in PQ.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, July 11, 2017 8:01 PM
    Moderator