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
drop
New Member

Auto refresh data from an Excel in Sharepoint

Hi,

I created a Power BI file using multiple Excel files in Sharepoint as a data source (Get Data -> Sharepoint folder -> combine Excel files). The Excel files have the query uploaded as data model.

Now if I manually open the Excel files and refresh data on them, my Power BI file get updated data. But what if I'd want to update data directly from Power BI? I'd need to schedule the refresh in Power BI to avoid to manually open and update the Excel files. I thought that with the data model I'd get this feature but it doesn't seem to work. Is there a way to do this or the manual update of the Excel files is mandatory?

Thank you

1 ACCEPTED SOLUTION

Hi @drop,

Power BI can't automatically refresh FTP data source. You can consider to use Power update to refresh CSV file connected in your Excel.


Thanks,
Lydia Zhang

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

5 REPLIES 5
nhenri
Frequent Visitor

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

ravali
Regular Visitor

Hi,

 

you wanted to get the data in excel file update by using power BI?

If yes, and if you found a solution, Please let me know.

 

Thank you!

v-yuezhe-msft
Employee
Employee

Hi @drop

Do you connect to SharePoint online folder or folder of on-premises SharePoint site in Power BI Desktop? Do you connect to other data sources in your Excel file?

If you connect to folder of on-premises SharePoint site, you would need to install gateway in order to set schedule refresh for your dataset.

Thanks,
Lydia Zhang

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.

Hi @v-yuezhe-msft,

Thank you for your reply.

 

I'm connecting to SharePoint online folder and the Execl file I'm connecting to is connected to CSV files in an FTP folder. I'd need to have the Power BI service to automatically get the updated data without manually opening and update the Excel file.

 

Hope you can help me with it.

Thank you.

Hi @drop,

Power BI can't automatically refresh FTP data source. You can consider to use Power update to refresh CSV file connected in your Excel.


Thanks,
Lydia Zhang

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
March Fabric Community Update

Fabric Community Update - March 2024

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

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors