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.
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.
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.
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.
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.
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?
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
It ask for OAuth2 credential, Then "Refresh" work fine.
Thanks for showing me the clue.
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.
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.
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.
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.