cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MojoGene Member
Member

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

Accepted Solutions
MojoGene Member
Member

Re: Confused about refreshing Excel file on local drive

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
Moderator v-yuezhe-msft
Moderator

Re: Confused about refreshing Excel file on local drive

@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.
MojoGene Member
Member

Re: Confused about refreshing Excel file on local drive

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)