Slow when refreshing data from Excel stored in SharePoint

[Anonymous]

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?

[v-yulgu-msft]

Hi @Anonymous,

Please try v-huizhn-msft's suggestion mentioned here to check if it helps to your scenario.

Regards,

Yuliana Gu

[Anonymous]

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]

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