cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
phyten Helper I
Helper I

Scheduled refresh for excel file with on-premise gateway

I installed the on premise data gateway and managed to get the connection running.

When I enter "manage gateways"  on app.power.com it tells me   "Online: You are good to go".

I also added data sources, (local excel files) which are on the same server where the gateway is installed and it tells me "Connection successful".

 

When I go to my dataset and click scheduled refresh it tells me:

"Your data gateway (PowerBI - personal) is offline. To refresh scheduling, make sure your gateway is online."

 

Why should I need a personal gateway if my on-premise gateway is already succesfully connected to the data?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Scheduled refresh for excel file with on-premise gateway

Hi @phyten,

You are using different paths to connect to Excel in Power BI Service and Power BI Desktop.

You connect to the Excel file using \\servername\issueexport\jiraexport_its.xlsx path in Power BI Desktop, you would need to use same path when adding Excel data source under gateway. There is an example for your reference.
2.PNG1.PNG

In addition, click option menu in the following screenshot, there is a option called"Not a solution", click it to undo the mark operation.
4.PNG

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

8 REPLIES 8
Microsoft
Microsoft

Re: Scheduled refresh for excel file with on-premise gateway

Hi @phyten,

Does your dataset only contain Excel data source?  If that is the case, firstly, right click your Excel file and select “Properties”, check the location of the Excel file, add excel name in the location and use the location as full path.
1.PNG


Secondly, check your user account in the Settings of your computer,  enter this user account(domain\user in my scenario) as Windows username when adding Excel data source under gateway, and enter relevant password. This way, you should have no issues setting schedule refresh for your dataset using on-premises gateway.
2.PNG3.PNG


However, if you combine Excel data source and other data sources in your dataset, you will also need to add other data sources under on-premises gateway. Otherwise, you will be prompted to use personal gateway.

 

 

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.
phyten Helper I
Helper I

Re: Scheduled refresh for excel file with on-premise gateway

Thank you for your answer @v-yuezhe-msft. This is pretty much what I did. 

In my published report I had two excel files and one folder but I also added all three sources to the data gateway.

 

Now I made a fresh report with only one excel file called "JiraExport_ITS.xlsx" and published it to the app. I still get the same "error" though.

I also made sure that the "Data Source Name" in "manage gateway" is the same as the "dataset" name in the app. (I am not sure if this is neccessary or powerbi automatically should detect the connection due to the filename"

gateway_online.jpgPicture of "manage gateway"blub.jpgNaming of the datasetdatasetgateway.jpgPicture of "scheduled refresh" for said data.

Microsoft
Microsoft

Re: Scheduled refresh for excel file with on-premise gateway

Hi @phyten,

Could you please post the screenshot of the data source setting in Power BI Desktop file? My data source in Power BI Desktop is as follows.

1.png

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.
phyten Helper I
Helper I

Re: Scheduled refresh for excel file with on-premise gateway

Hello @v-yuezhe-msft. Sorry for the late response. I was on vacation.

2017-01-10 09_52_05-.jpg

 

The sourcefile is located on a server. The gateway is installed on the server. I am publishing the pbix file on a different machine though. Could this be the mistake?

If this is the problem. Do you have an idea how I could fix this?

phyten Helper I
Helper I

Re: Scheduled refresh for excel file with on-premise gateway

By mistake I marked one of the posts as an accepted solution. Is there a way to undo this?

Microsoft
Microsoft

Re: Scheduled refresh for excel file with on-premise gateway

Hi @phyten,

You are using different paths to connect to Excel in Power BI Service and Power BI Desktop.

You connect to the Excel file using \\servername\issueexport\jiraexport_its.xlsx path in Power BI Desktop, you would need to use same path when adding Excel data source under gateway. There is an example for your reference.
2.PNG1.PNG

In addition, click option menu in the following screenshot, there is a option called"Not a solution", click it to undo the mark operation.
4.PNG

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

TimHellegers Helper III
Helper III

Re: Scheduled refresh for excel file with on-premise gateway

Hi @v-yuezhe-msft ,

 

I've a question about using Excel and de data gateway. Our company is going to use the PBI service with the gateway to let employees collaborate. All the employees have a network share connected on their laptop, however the share letter differs. The data gateway will be installed on a server that has access to the same network share but the share letter could differ as well.

 

For example;

Andy has a network share 'Files' using share letter (Y:). John has the same network share but with share letter (X:). The server (with data gateway) has access to the same network share, but uses the share letter (Z:).

 

How can I make sure that the datagateway can connect to the right file using a different share letter?

u02cm62 Resolver I
Resolver I

Re: Scheduled refresh for excel file with on-premise gateway

you use UNC path naming convention.  Like this \\servername\directory1\subdirectory2\filename.csv.  If you use a drive letter and expect everyone to use it other than yourself, you must have the file on your machine in the same location as C: or D or whatever drive letter has to be on every computer in that example, so using unc path resolves it to ONE path that you all have access to and not use a drive letter to reference it.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors