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
MojoGene
Post Patron
Post Patron

Confused about refreshing Excel file on local drive

I have an Excel workbook that is periodically (manually) updated by users in our organization. There is one sheet in the workbook that has been converted to table (Ctl-T). It is stored on a network drive.

 

I created a PBIX file, imported the Excel workbook table into the data model, prepared a report based on the data and published the file to the PBI service online.

 

I have an enterprise gateway installed and it is working fine with many other datasets that connect to a SQL server on the same network. The enterprise gateway also connects to and properly updates Excel files that are stored on my local c: drive.

 

The Data Source Settings show that I have a "Successful" connection from the PBI service to the network Excel file. 

 

When the network Excel workbook is refreshed the PBI service shows that there was a successful update, but the data are NOT being updated.

 

Not sure what I am missing here. Thanks.

 

 

1 ACCEPTED SOLUTION

Lydia

 

thank you for your reply. The problem was something that should have been obvious to me. In order for the Excel to update (there are some calculated columns in the worksheet that calculate the number of days remaining to an event) the file needs to be opened first. I was under the mistaken impression that refreshing the PBI dataset would accomplish this.

 

I was able to solve the problem with a scheduled task in Windows that is set to run just prior to the scheduled PBI refresh. It opens, saves and closes the Excel file. 

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

@MojoGene,

Do you set schedule refresh for the dataset, what does the network path look like? And when you manually refresh the dataset using “Refresh Now” in Power BI Service, does the dataset update? 

In addition, we will appreciate that if you can share us the Excel file. I will test it in my environment.

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

Lydia

 

thank you for your reply. The problem was something that should have been obvious to me. In order for the Excel to update (there are some calculated columns in the worksheet that calculate the number of days remaining to an event) the file needs to be opened first. I was under the mistaken impression that refreshing the PBI dataset would accomplish this.

 

I was able to solve the problem with a scheduled task in Windows that is set to run just prior to the scheduled PBI refresh. It opens, saves and closes the Excel file. 

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