SharePoint - Modify Query

[JillHenninger]

Hi all

I have a SharePoint list of about 50,000 projects (my company uses the task list to request and assign projects and to track time). I've created a comprehensive tracking summary in Power Pivot - and now am trying to replicate in PowerBI. There are 41 columns, and about 57,000 rows/entries. There are 12 lookup fields.

In Excel, I've exported the list from SharePoint to Excel, connected it as data source to my summary file, then manually refresh both each week. The plus of Power BI is conencting directly to SharePoint so any user in the group can refresh it. But - given how PowerBI is bringing in the lookup columns, I need to modify the query to show me names and not "list" or numbers. And, I can't get it to actual apply the query changes. 

I went to Get Data, SharePoint Online List, pointed to my site, selected the right list, and connected it. That took ~30 minutes to bring it in. I went to Edit Queries, then Expanded the 3 of the 12 columns. I thought I'd start there before modifying all of them. (Also, it renders 95 columns instead of 41 and renamed most of them - it's a mess). I hit apply - and it ran for 12+ hours. I finally had to cancel it.

What am I doing wrong? Is there a different way I should connect the data? Is there a different way to render the columns properly? I've tried to Google it, scour forums - and I'm coming up short. 

TIA

Jill

[v-piga-msft]

Hi @JillHenninger ,

It seems that you have problems when you get data from SharePoint Online list in Power BI Desktop?

Based on your description, it seems that your steps should be right. 

Please check if you have installed the latest version of Power BI Desktop 2.66.5376.252 firstly .

---

I went to Get Data, SharePoint Online List, pointed to my site, selected the right list, and connected it. That took ~30 minutes to bring it in. I went to Edit Queries, then Expanded the 3 of the 12 columns. I thought I'd start there before modifying all of them. (Also, it renders 95 columns instead of 41 and renamed most of them - it's a mess). I hit apply - and it ran for 12+ hours. I finally had to cancel it.

---

Normally, if you get data from sharepoint online list, we will see that it will load more than the columns that we created in sharepoint.

You could select the columns that you need and click remove other columns in query editor.

In addition, if your data is a little large, it do will take some time to load.

Best  Regards,

Cherry

[JillHenninger]

Thanks! I am indeed using the latest version - 2.66.5376.2521 64-bit (February 2019)

Edit Conern:

I've now tried doing it via odata (using the site then /_vti_bin/listdata.svc) and have the same lag both on editing the query and in updating the data. The set is 1.5 million data points. It took at least 24-48 hours to execute 9 Expand Column steps (I had to leave it over the weekend). Subsequent edits take hours (even for one Expand).

---

Refresh Concern:

I tried refreshing just now - and it took longer than I had available to spend. I thought using PowerBI would allow a much more nimble process for my team to access insights - but users aren't likely to wait hours for the refresh. I guess I could schedule it to run at say midnight each day?

The Sharepoint is 365/Online, and it's hosted in the cloud not on prem.