cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sivapratap Member
Member

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.

sivaaprataap
9 REPLIES 9
Super User
Super User

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

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.


Check-out my Back to School contest submission: Dinosaurs!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

sivapratap Member
Member

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

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

sivaaprataap
ehakimi Frequent Visitor
Frequent Visitor

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

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 

Super User
Super User

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

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.


Check-out my Back to School contest submission: Dinosaurs!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ehakimi Frequent Visitor
Frequent Visitor

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

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,

Super User
Super User

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

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.

 

????


Check-out my Back to School contest submission: Dinosaurs!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ehakimi Frequent Visitor
Frequent Visitor

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

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,

 

ehakimi Frequent Visitor
Frequent Visitor

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

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.

 

MarioLorenzo Regular Visitor
Regular Visitor

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

Oh god!

 

I had the same problem,

 

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 95 members 1,885 guests
Please welcome our newest community members: