Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Fetch Live data from Sharepoint Online List and publish it as a data set to Power BI Online

Hi Team,

 

We have a requirement from business saying the data stored in one or more SharePoint list (SharePoint Online) needs to be  fetched as a data set, so that the user logs-in to Power BI online and generate Ad-hoc reports based on the live data. As per my understanding on Power BI, to enable the live data, Schedule refersh needs to be enabled for the sync. So, what exactly i need help on is , basically understand how to enable the connection between O365 sharepoint list data and Power BI online. Any architectural reference will definetely help. Please share any tutorials on this implementation.  

 

Is it necessary that first connect to sharepoint list data and create a data set using Power BI Desktop and publish the same on to Power Bi online. But, in this case what i understood is, the schedule refresh triggers a connection between power Bi desktop and online and, not between Sharepoint list and Power Bi online. So again to get the latest data, i need to refresh the data set in desktop version, so that on next schedule refresh the updated data in desktop version will be posted to power bi online. Please correct if i am wrong.

 

Please provide me necessary pointers to proceed with this implementation. Thanks in advance.

 

Regards,

Sivapratap.

9 REPLIES 9
Greg_Deckler
Super User
Super User

If you create a Desktop file and have a query to a SharePoint Online list, when you publish that file to the Service, Power BI Online will refresh the data from SharePoint Online list based upon your schedule.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Smoupre,

 

 

Thanks so much for your fast response. I just have one account in the company and don't have access to spadmin and other accounts.

My account has Full Control on the site so permission shouldn't be the issue.

I am gussing that should be an error on the service!

I was thinking due to lots of changes on my dataset, it might be damaged but I start with just one fresh table as test and same rersult.Capture53.PNG

 

Thank you,

I just tried to replicate this using my SharePoint Online tenant. Could not. I connected to our root site and chose an Announcements list. Did not edit the query in any way, just loaded the data. Published to Power BI Service. Confirmed there was data in the list by going to the dataset and selecting "GUID" in a table visualization. Edited the credentials in Schedule Refresh to be Oauth instead of Anonymous. Did a Refresh Now by clicking on the ellipses next to the dataset. Confirme refresh succeed. Went back to dataset and clicked on "GUID" in a table visualization, the data was still there.

 

????


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Smoupre,

 

You are absolutly right. It work very well.

I have reproduce it with announcment list (no chnage on dataset) and it work.

 

The only problem in my case is that I am using below tip in my dataset (expand approch), because I have multiple value fields in my list.

https://www.itunity.com/article/simplified-method-working-sharepoint-data-power-bi-3451

The issue happen if you are change dataset and Expand "FieldValuesAsText".

Do you know any other approch for list that are including Manage Metadata, Lookup and Multiple value fields?

 

Best regards,

 

Ok here is a work-around,

If I change the way that I get Data from SharePoint and use OData instead of directly connect to SPO list, that will fix the refresh issue.

So In PowerBI Desktop, Instead of directly connecting to SPO list, I use OData as below

https://SPO-host-name/sites/subsite/_api/web/lists/getbytitle('list_name')/items

It ask for OAuth2 credential, Then "Refresh" work fine.

 

Thanks for showing me the clue.

 

Anonymous
Not applicable

Oh god!

 

I had the same problem,

 

when i upload the report, the dates of it change, it's so strange....

Hi Smoupre,

I have try this several time this week and unfortunatly it won't work.

So I create a dataset in PowerBI desktop which connect to SPO list. Then create some reports based on that and publish it.

It work well and show all report.

Then as soon as I do one of the below:

1- go to PoweBI online and click "Refresh Now" on the dataset

2- go to PoweBI online and click "Schedue Refresh" and set Daily on the dataset

As soon as data refresh, All the data gone and I will have a blank report without any data.

If I come disable refresh and open report on PowerBI Desktop and publish it again, data will be appear again.

Could you please explain me what else should I do? I have done it 3 times with a fresh dataset as well. 

 

Thanks 

That's pretty strange. There could be an issue with the Service. I'll have to check to see if I can replicate your issue. The only other thing I can think of is if you are using different OAuth credentials between Desktop and Service to connect to your SharePoint list and the Service credentials you are using do not have permissions to see the list items.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Buddy,

 

Thanks for the answer. Need few more clarifications on creating a data set from SharePoint Lists(online). As per the user requirement, he\she needs a dataset created using all the columns present in two SharePoint lists. To throw some light on the scenerio, post merging data into single table from the both the lists, the column count has exceeded to 200 + columns, When power BI was trying to load the preview with the merged table , preview failed due to large count of columns (exactly not sure on the error message). Can you please let me know  what exactly is the column limit  to create a table using merge query.  This will help us in communicating to the business. Thank you.

 

Regards,

Sivapratap

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors