cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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.

 

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors