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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nhenri
Frequent Visitor

Automatic dataset refresh from Excel data source on Sharepoint

Hi all,

 

I've been testing Power BI recently to offer powerful KPI to my project team "on top" of their usual Excel reporting files, stored on our Project Sharepoint.

I read many articles stating that in this type of configuration, the PowerBI reports should be updated automatically as soon as the data is updated in the Excel stored on Sharepoint (maybe with 1 hour delay...), and this is not my case.

Let me give you some more details on the set-up I'm using:

1- The data used for this dataset/report is basically a TABLE declared in the Excel workbook

2- The Excel workbook is stored in a Sharepoint (equivalent to a Business OneDrive)

3- I built the report/dataset in Power BI Desktop, performing a GET DATA and using the EXCEL connector (then browsing thru my company Sharepoint folders and connecting to the right Excel workbook

4- Then I published the report to Power BI services, no issue until that point.

 

In term of data update:

- In Power BI Desktop, I don't expect to have any automatic refresh (when I edit the report agin for example. But when needed, I can refresh the DATASET pressing the REFRESH button --> no issue.

- In Power BI Services, clicking on the Refresh button in the Report view just doesn't trigger any action: no update, no error message...

But when asking for a DATASET refresh, I just receive an error message starting with "Refresh failed due to gateway configuration issues".

 

Do you have any idea of waht I missed? I really thought that on this type of set-up, the dataset stored in Power BI Services was refreshed automatically from the Excel data stored on ONEDRIVE Business/Sharepoint...

 

Thanks for your guidance,

Nick.

PS: I'm still using the Power BI Pro evaluation period...

 

1 ACCEPTED SOLUTION

@nhenri ,

If you are using on-premises SharePoint, gateway is required. If your are using SharePoint online, gateway is not required, just change data source of your Power BI report in Power BI Desktop.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
edhans
Super User
Super User

When you say Sharepoint, you mean Sharepoint Online via Office 365? That should refresh automatically.

 

But when you say your gateway isn't supported, that tells me you are mashing up data. You don't need a gateway for Sharepoint Online files, and once you mash up data with on prem (gateway) and oneline data, you must do a scheduled refresh, and you have to configure your gateway to allow online data to refresh through the gateway in the gateway config.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
nhenri
Frequent Visitor

Hi,

you're right: Sharepoint online for O365! and yes, "automatic refresh" is what I expect.

 

To clarify the "gateway" topic:

- I didn't deploy any, as I'm expecting the refresh to work "as is"

- nevertheless, when requesting a manual refresh of the dataset in Power BI services (my report doesn't update automatically), I receive an error message as if I'd have to configure a gateway (see attach.)

 

Thanks for helping

 

PBI refresh error message.png

 

That isn't SHarepoint Online for Office 365. That file path is a local file path to an on-prem sharepoint server. You have to install and configure a gateway.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
nhenri
Frequent Visitor

@edhans @v-yuezhe-msft 

Thank you guys! I guess I learnt what "on-premise" files means :-). My company indeed uses its own servers for our cloud based Office365 deployment.

Based on your feedback, I understand that I don't have any other choices: I need to deploy a gateway.

 

Thanks again to nail it down quickly.

@nhenri ,

If you are using on-premises SharePoint, gateway is required. If your are using SharePoint online, gateway is not required, just change data source of your Power BI report in Power BI Desktop.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I've been confused on this for quite some time searching for the difference between SharePoint Online and SharePoint On-premises and why I'm receiving a "Dataset requires a proper configured Gateway..." error like above. This thread is the closest to any information I can find out on the difference but still don't have all the information!! I wish there was an article to provide the SharePoint difference AND how to resolve the Gateway issue. (Troubleshooting Gateways from docs.microsoft.com didn't help). 

Via Desktop, when you 'Get Data' and select SharePoint Folder - then access the datasets (all separate excel files stored in a SP Folder), once you publish to the Service, you receive the Gateway error message. It sounds like you need a Gateway IF you Get data this way. However, if I use via Desktop 'Get Data" and select 'Online Service' and Select 'SharePoint Online List'. That's the only thing you can use - a list on Sharepoint, it will auto-refresh the data in the service. What I need is to use indiviual excel files stored within a Document Library folder. It's almost like there isn't an option to use individual excel files stored on a SharePoint Document Library via Online so that I won't need a Gateway?  If you know how to do this, please drop in the instructions. I've read so many articles & threads but nothing seems to pinpoint a solution on this issue.

1. This doesn't work without a Gateway? https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/

2. You don't need a Gateway but can't access individual excel files in a SP Doc Libarary unless you use OData? (have yet to try this)  https://community.powerbi.com/t5/Integrations-with-Files-and/Fetch-Live-data-from-Sharepoint-Online-...

3. Another idea with Sharepoint; Use Sharepoint - Teams and it will work like OneDrive but in SharePoint?? https://docs.microsoft.com/en-us/power-bi/service-comma-separated-value-files

 

Bottom line, I have excel & csv files stored in a SharePoint Document Library. Those files are updated weekly via a Microsoft Flow that overlays the files each week. The PBI reports were created via Desktop and Published out to Power Bi Service. I need Power Bi Service to REFRESH the reports (Dataset) automatically WITHOUT me having to go into Destop, hit Refresh and then Publish again to the Service overlaying my existing reports. 

@AWentzEco  - SHarepoint Online and Sharepoint on Prem are the same basic product, at least from the end user perspective. If publishing Power BI reports though you have to know which you have.

 

When writing a report in the Desktop, if you are at work, it will connect with no issues to both On Prem and Online, because you are there. But once published, the Service has no way to get to your on prem server without a gateway. It can connect to Sharepoint Online with no gateway, you just need to add your credentials.

 

If you are getting a "needs gateway error" you must have on prem for your data source, and you need a gateway so the service can reach it. Think of the gateway as a VPN that allows the service to get to on prem resources. Can be anything almost. SQL Server, Sharepoint on Prem, network drives, Access databases, etc.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans  Thanks for the clarification. This definitely helps.  But I'm still confused on how to access SharePoint Online when using 'Get Data' via Desktop. It's like the only option is a SharePoint List - but there must be a way to use SharePoint Online, then use/link to individual files stored in a Doc Library.  I cannot find any instructions on how to do this....but there must be a way. That way, I would be linked to SharePoint Online and can use the Refresh in Service.  

 

***Updated****

Ok, I stand to be corrected. I created the Data Source links quite a while ago. I thought I originally went in and linked via SharePoint folder, then changed "SharePoint.Files" to "SharePoint.Contents" - found the Folder, grabbed each file individually. 

Looking back at my Queries for each table, it appears I connected via 'Text/Csv' since all I have is:

Source = Csv.Document(Files.Contents("C:\Myinfo....") for each one.  

With connecting via SharePoint the steps should have been:

Source = SharePoint.Contents("https://....)

Navigation = #"Your Document Library Name{[Name=".."]}[Content]

Imported CSV = Csv.Document....

 

Which I believe will actually allow for a Refresh on the Service without a Gateway.  (That was a lot of research effort for such an idiot mistake on my part.)  Rule #1, always look back at your Source Step...

@nhenri ,

>>I built the report/dataset in Power BI Desktop, performing a GET DATA and using the EXCEL connector (then browsing thru my company Sharepoint folders and connecting to the right Excel workbook

 

The Excel connector can only access to an Excel file stored in an on-premise folder, the Excel file you connected is just a synced file with Sharepoint online, it is still a local file. In this scenario, we will need to configure a gateway to refresh the on-premise file.

 

If we want to use the Excel in Sharepoint Online, we can use the Sharepoint Folder connector or Web connector. Please refer to the following blogs to get detailed steps to configure them.

 

Using the Web connector: https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links

Using the Sharepoint Folder connector: https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/

 

In your scenario, you can replace the original on-premise source to the Sharepoint Folder or Web source using the following steps, this way, you don’t have to re-create the whole report and it doesn’t require gateway to refresh data after you publish PBIX file to Power BI Service.

 

  1. Open Power Query Editor by clicking the Home -> Edit Queries.
  2. Then refer to this blog to replace queries: https://www.decisivedata.net/blog/change-a-power-bi-data-source-type

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors