Forum Discussion

nhenri's avatar
nhenri
Frequent Visitor
7 years ago
Solved

Automatic dataset refresh from Excel data source on Sharepoint

*** Re-publishing as a new post***

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

9 Replies

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    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.

    • nhenri's avatar
      nhenri
      Frequent Visitor
      7 years ago

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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