Confused about refreshing Excel file on local drive

[MojoGene]

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.

[Anonymous]

@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

[MojoGene · 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.