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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ronnoc
Advocate II
Advocate II

Refreshing data from sharepoint spreadsheet

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.

 

error6.PNG


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

1 ACCEPTED SOLUTION
ronnoc
Advocate II
Advocate II

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:

 error6.PNG

 

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.

View solution in original post

1 REPLY 1
ronnoc
Advocate II
Advocate II

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:

 error6.PNG

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors