Refreshing data from sharepoint spreadsheet

[ronnoc]

Firstly thank you to everyone who has been so helpful with previous posts, this community has provided excellent support.

I have a spreadsheet stored in SharePoint online, I have imported the data into PowerBI desktop from the spreadsheet and created a PowerBI file which has been published to the PowerBI service.

Everything works except that when I make a change in the spreadsheet via sharepoint, it is difficult to see whether or not the data is updating properly in powerBI desktop and service. After the first alteration of the sharepoint spreadsheet, it seemed that the updates were reflected quite clearly when pressing the refresh button in PBI desktop and PBI service.

On all subsequent attempts the modified data does not appear at all on the PBI service or desktop. PBI desktop throws errors that the document isnt a valid worksheet and the refresh button in PBI service doesnt appear to trigger any click events whatsoever.
I understand a personal gateway is required for scheduled refresh of on-premise data but sharepoint is a online source which should update and refresh within at most an hour or so of any data alterations.

Should we not expect complete functionality of the sharepoint integration features at this stage? Any help is appreciated.

[ronnoc · Accepted solution]

Managed to answer my own question so will post the solution incase any struggles in future. 

Sharepoint service does update in powerBI and can be done in the following steps:

1. Get data from sharepoint folder, use log in credentials to verify access to the files

NOTE: IF you get errors just use the master directory! not a specific folder and check your url is correct.

2. This returns a list of all the files in all the subfolders of the master sharepoint directory

3. Find your data in my case it was a simple excel spreadsheet, isolate it by removing all other rows and then expand the data

4. Transform data if you need to, apply changes and build reports, publish to powerBI service etc etc

5. Most important step! Set the schedule refresh settings and press refresh now by right clicking on the dataset in PBI service. See here:

 

You should now be able to use lists and spreadsheets stored in sharepoint and set them to update and refresh weekly/daily/hourly to keep your dashboards and reports up to date.