On-Premise Excel Refresh in Power BI

[hemal_kanjia]

Hello,

I have created the Power BI Report from Excel file which is in my local server.

Just Note that I have not created Excel Data Model.

Now, I want to put Schedule refresh between Excel File and Power BI Online.

The Server which has that excel file has no internet connectivity.

The Gateway installed in the other server which has internet connectivity.

Both the servers are in same network.

It is not possible to put Excel file into SharePoint or OneDrive.

If it is possible to put Schedule Refresh, can you please guide me how to put the schedule refresh ?

Thanks in advance.

[v-danhe-msft]

Hi @hemal_kanjia ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[hemal_kanjia]

Hi @v-danhe-msft ,

Thank you for the reply.

I am still getting an error and not able to put Schedule Refresh.

I have also follow below step suggested by you.

Here is the Scenario:

• I have my excel file in File Server (On-Premise)
• I have installed the Gateway in another On-Premise server (I can't install the Gateway in File Server, because we have created Gateway server in which we have installed the Gateway.
• I have one Service Account (serviceaccount@domain.com) which is in AD.
• Both the servers (File server and Gateway server) are in same network and also synced with AD and workgroup.
• Now, I have tried to put the schedule refresh with service account; but not able to put.
• For testing purpose, we have installed the Gateway in one Azure VM and tried to put the schedule refresh with service account, and I was able to put the schedule refresh. but not able to put schedule refresh by using gateway installed in On-Premise server.
• Please note that, the service account is able to access both the servers (File and Gateway) because both the servers are connected with AD.
• Do I need to use some Workgroup account to put the schedule refresh ? If yes, is it the best practice ? What I need to do If I want to put schedule refresh with service account ?

Your help will be really appreciated.

Thank you.

[v-danhe-msft]

Hi @hemal_kanjia,

Based on my test, you could convert your excel data to table(Ctrl+T):

And use the Power BI Desktop to get this excel data and published to the service, configure the gateway:

Now it could set the sechdule refresh:

Regards,

Daniel He

[hemal_kanjia]

Hi @v-danhe-msft

Thank you for the answer.

But I have some concerns here.

(1) My Excel file is generating from SSIS, so the data is directly stores into sheet.

(2) My Gateway is in another PC which is in Network with the server where this excel file located. Can i use that gateway to refresh the data ? or should I need to install another Gateway in the server where the excel file is located.

(3) While configuring the Gateway, which username and password should i need to use ? (for ex, when using SQL, we enters Database UserName and Password because there is an option to choose authentication type(windows, Basic etc) when source is SQL.)

(4) What is the file path should i need to enter ? Local Path (C:\Excel Folder\FileName.xlsx) is not working. It gives an error that invalid file path.

Thanks

[v-danhe-msft]

Hi @hemal_kanjia ,

Could you make sure that your excel file is saved in your local computer? The gateway is used to refresh the on-Premise data, so I suggest you install the gateway withe your local file in the same computer.

When you configure the gatway, the Local Path (C:\Excel Folder\FileName.xlsx) must be the local file and the username and password is your windows account, you could also refer to below link:

https://docs.microsoft.com/en-us/power-bi/service-gateway-manage

Regards,

Daniel He