Auto-refresh with Static Excel Files

[khappersett]

I am trying to setup auto-refresh on my reports, but seem to have problems with the reports including excel files. I created my date tables in Excel and brought those into my PBI reports. These Excel files are static and do NOT need to be refreshed with the rest of my report. I have unchecked this option in the query options...

I have successfully setup my gateway and have no problem scheduling refreshes on reports without these excel files, but when I try to schedule my refreshes on these reports, I get this error....

Not sure what to do since I already unchecked the box to not include these in my refreshes. Also don't want to remake my date tables in PBI since I have many reports that would need changing. The area to edit my data source credentials on the browser is also greyed out.

[MattAllington]

Well it seems the report wants the gateway needs to be set up regardless. So why don't you try that. If it solves the problem the. All good, right?

[khappersett]

I already have my gateway setup and everything works fine when I don't have the excel files in the report.

[MattAllington]

There is 1 gateway, but multiple configurations.  Each file needs its own configuration.  My guess is that these files are not configured in your gateway.

[khappersett]

Hm not sure I know what you mean. I can take literally the exact same PBI file and remove just the excel portions and it works on my gateway just fine, so it is definitely the Excel files causing the problem. Why would I need to configure these parts if I don't want them to refresh?

[khappersett]

I still have not found a solution to this problem. I had to take all my excel files out of the reports to get the refreshes through the browser to work. This can't possibly be the only solution as I have other reports that still have excel files that need to be auto-refreshed...

[Anonymous]

Where are your Excel files being stored?  Is that data source included in your gateway configuration?

[khappersett]

@Anonymous

They are stored locally on my computer. I do NOT need these files to be refreshed, as they are static. I do not have any gateways setup for the excel files. If I try to "Add Data Source" under manage gateways, I do not see an option for an excel file. If I choose "file", it asks for windows credentials and my login info is not working.

[khappersett]

Do I need to setup a Personal Gateway? I am hoping this is not the case because I want the reports to refresh even when my computer is not turned on.

[Anonymous]

I would set up the personal gateway so that the Power BI Service can load them the first time.  Verify that it works then test without the gateway.

[khappersett]

@Anonymous

It works when I setup the personal gateway, but now the option to switch to the gateway I want to use is greyed out. We have our gateway installed on a virtual machine in IT so the "computer" is always turned on and able to be accessed for the refreshes. I do not want to use a personal gateway setup on my computer.

[Anonymous]

Not sure, but usually that means that your data sources in your PBI file dont match your data sources in the configuration for the On Premise Gateway.  In order to schedule a refresh from the PBI Service, it has to know how to get to your data files.

[khappersett]

@Anonymous

I'm not sure how to configure these excel files with the on premise gateway. I don't see a way to do this anywhere. 

[Anonymous]

If you are going to use the on-premise gateway, the files need to be on a share that is accessible to the on-prem gateway.

On the gateway, you need to add a datasource point to that share where the files are.  When you build it in PBD desktop, you have to use the EXACT share location that you have configured in the On-Prem gateway.  They both have to point to the same UNC because that is how the Power BI Service knows that it can access the files there.

To add the datasource, you need be an an admin in the On-Prem gateway.  Once you are an admin, go into Manage Gateways in the service and add your datasources that point to the files.

[khappersett]

@Anonymous

Thanks! I believe this is the answer I am looking for. I was hoping there was a way where I would not have to configure the excel files because I am NOT trying to refresh them. I only need to refresh the SQL queries within the same report. It seems as though this is not that case and I have to configure the excel files too even though I don't want them to be refreshed.

[Anonymous]

AFAIK If they show up as data sources in your PBIX file then the Power BI Service will need to know how to access them.

[Greg_Deckler]

Looks like you need to upgrade your gateway? From what I am seeing, it looks like your gateway isn't online.