Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Slow when refreshing data from Excel stored in SharePoint

I have connected Power BI desktop to an Excel spreadsheet in SharePoint which contains about 100k rows (and 76 columns although it actually loads in a lot more blank columns which I remove taking the number up to 122) but it is very slow to refresh (2-3mins) and also makes performing edits to the Query steps very cumbersome.

 

Does anyone know how I can speed this refresh up or any tips for how people develop with large datasets, I did try only keeping the Top 1000 rows but that still loads all 100k and then keeps the first 1000 so it doesn't speed the query up.

 

Or is it the case that connecting to an Excel file in SharePoint is always slow?

 

 

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Please try 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-yulgu-msft,

       

      Thanks for linking the other post, however in terms of refresh time it doesn't make a difference, although disabling the background refresh of the Preview in the Query Editor does help if you need to make several changes across many steps, so that is useful to know!

       

      FYI - when I click refresh it spends a lot of time just 'Evaluating', any idea what this is doing?

       

      Thanks,
      TG

      • steph_io's avatar
        steph_io
        Icon for Advocate II rankAdvocate II
        7 years ago

        Did you find a resolution to this issue? I have the same problem even after I have applied the suggested steps.