External data in SharePoint Refresh Issue

[Anonymous]

Created excel file with 2 sheets of plain table data and 3 sheets with pivot table connecting to tabular model(external data connection). Developed Power Bi report off of this file and published in a workspace.  Right now I manually refresht he excel file and refresh Power BI and re-publish daily, is there a way to automate this refresh?

This file is saved in sharepoint site but need help to understand how this file can be refreshed automatically including the data refresh, so that power bi displays latest data.

 Please help

[v-yiruan-msft]

Hi @Anonymous ,

How did you connect the excel file in Sharepoint site and created the report in Power BI Desktop? Whether you use web connector with excel file from sharepoint online or other method? If you created the report based on Excel workbook on SharePoint Online, Power BI performs OneDrive refresh. OneDrive refresh synchronizes the report with their source files. By default, Power BI checks about every hour if a dataset connected to a file on SharePoint Online requires synchronization. You can check the details in the part "OneDrive refresh" of this documentation.

Best Regards

Rena

[Anonymous]

Thank you, I used the analysis service connection in Excel.

[v-yiruan-msft]

Hi @Anonymous ,

Whether you connect the excel file in sharepoint using the method in below screenshot? 

https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/

Best Regards

Rena

[GilbertQ]

Hi there

Yes this certainly can be done using the Power BI On-Premise Data Gateway

Here are details on how to get this done

https://docs.microsoft.com/en-us/power-bi/refresh-desktop-file-onedrive

[Anonymous]

Thank you I will try this and keep you posted if it worked.