SharePoint Online List- Power BI - slow query update

[sham_powerBI]

I am trying to create a dashboard using Power BI desktop app. My data source is a SharePoint Online list. I updated the data setto clean it up. the issue that when I tried to apply changes to the query update the data set it takes forever. I only have about 4000 rows of information and about 25 columns but the data does not update even after 3-4 hours.

The data keeps on increasing but a very slow speed. Please let me know if there is a better way to update the query and data set.

Thanks

[Eric_Zhang]

@sham_powerBI

What changes are made in the "query editor"? How was your PC running, is there any process cosuming too much cpu, memory or dist IO? Have you tried to deleted the datasource and get data again? 

[sham_powerBI]

@Eric_Zhang 
My PC was running at a 40-50% CPU utilization, memory and I/O. I havent tried deleting the data source and getting the data again, but then I would still need to update the query to clean up the data.

The apply chnages process finally complete after 5 hours for about 60 MB of data. I think that is really slow just to update the query.

[Eric_Zhang]

---

@sham_powerBI wrote:

@Eric_Zhang 
My PC was running at a 40-50% CPU utilization, memory and I/O. I havent tried deleting the data source and getting the data again, but then I would still need to update the query to clean up the data.

The apply chnages process finally complete after 5 hours for about 60 MB of data. I think that is really slow just to update the query.

---

5 hours for 60MB data is absolute not normal. How is your network condition? I don't quite know SharePoint Online, is there any traffic threshold set?

Regarding the bold part, how do you clean up the data? Can you clean it up in DAX, just leave the query in "M" as is?

[squirino]

I have the exact same problem.

Long loading time (refresh time) from SharePoint Online, low computer processing usage, I work on a network engineering company so although I haven't checked the network health I have to believe it is healthy. (by the way, I have the same issue when running it from home in a 150 mb download network).

[gpiero]

I have tried a connection and updating just now:

Query # 1

Updated 2500 rows about 5 MB in a 11 minutes

Query # 2

Updated 8500 rows 9MB in a 2 minutes.

The first query is a bit slower since the SP list has some lookup field in to 2 o 3 lists.

Both connection tried just now in a Wi fi mode.

Definitly there is something to check in a network or in SP admin area

I hope this data could be helpfull.

[trebgatte]

Wow, that is incredibly slow. Are you using the REST API or the built-in SharePoint Online List connector? I have a thousand row list that refreshes in seconds. How big are the lookup lists? I wonder if it's attempting to query those as well.

If you are using the SharePoint Online List connector, try it with the OData connector and use the following url constructor. https://YOURSITE.sharepoint.com/SITENAME/_api/web/lists/GetByTitle(‘LISTNAME')/Items()

Treb Gatte | Business Solutions MVP/ Power BI Showcase Partner | Power BI Recordings | @tgatte | Blog 

[gpiero]

@trebgatte

Many thanks

It works in a woderful way

I have updated Query #1 ( just for test) in 25 seconds

let
    Source = OData.Feed("https://mysite.sharepoint.com/sites/Wmsh/it/Shpmt/SH_OutMgt/_api/web/lists/GetByTitle('Lista consegne per periodo')/Items()"),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"ID", "ID.1"}})
in
    #"Renamed Columns"

Thanks again

[gpiero]

Just an update:

the query instructions after the example above make the difference.

I copied my report using the same query but the first one links using OData.Feed and I left the other as it was at the begining.

Both take basically the same time to refresh data.

Are there CBA to improve the query execution?