Install And Configure Data Gateway For On-Premise Excel Files In Power BI

Overview

In this article, we will learn how we can install Data Gateway with the Enterprise mode and configure an on-premise Excel file connection with Power BI Service.

We have the following types of scenarios.

We have used multiple Excel files which are located on one of the local servers.
We need to consume those Excel spreadsheets and need to prepare Power BI report.
We need to configure the scheduled refresh for the report as well.

Now, let’s get started!

Installation of Data Gateway

Step 1

Download the set up using the following URL.

http://go.microsoft.com/fwlink/?LinkID=820925

Step 2

Right-click on Setup > Run as Administrator.

Install and Configure Data Gateway for On-Premise Excel Files in Power BI

Step 3

It is showing two options -

Personal Mode
Recommended Mode

Here, we will choose the "Recommended Mode".

Choose Recommended Mode > click Next.

Step 4

It will prompt a message like this.

Step 5

Choose a path for installation.

Step 6

Enter the Power BI work email address and click "Sign In".

Step 7

Once the authentication is successful, it will ask to register a Gateway for On-Premise Data Gateway.

Step 8

Now, let’s fill in the following information to configure a gateway.

New On-Premise Data Gateway Name = Name of a Gateway which you want to create.
Recovery Key = any 8-character password to recover a Gateway.

Step 9

It shows a message “The Gateway is Online and ready to use”.

Configure Gateway for On-Premise Excel Files(s)

Step 1

From Settings gear, open "Manage gateways".

Step 2

It will show the name of the Gateway which we have configured. Click on “Add data source to use the gateway” option.

Step 3

Add the following information.

Data Source Name = Name of the Data source name. You can add any name here.
Data Source Type = We are using multiple Excel spreadsheets which are located at one of the Windows location folders. So, we choose Folder from the option.
Full Path = Path of the folder where we have located all the files.
Username = Username for your Windows machine.
Password = Password of your Windows machine.

Install and Configure Data Gateway for On-Premise Excel Files in Power BI

Click on Save. It will prompt - "Connection is successful".

Step 4

From the dataset, select your report and click on "Schedule Refresh".

Step 5

From Gateway connection, toggle "Use a data gateway" as turned on.

Click "Apply".

Step 6

It will show a notification.

Step 7

Let’s configure a daily refresh.

Expand the "Schedule Refresh" option.

Toggle On and select Daily from Refresh Frequency.

Click Apply.

Conclusion

This is how we can configure a data gateway for Excel On-Premise file.

I hope you love this article.

3 Comments

Join the conversation! Your thoughts help the community grow.

Guram Jalagonia4y ago
Hello dear Dhruvin. I have a next problem: "Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Show detailsTroubleshoot connection problems." Can you help me?
Nalaka Nayanakantha7y ago
Is there a way to set up online data refresh service,means not installing gateway in your local PC.
- Dhruvin Shah7y ago
  If you are using Excel Online, you don't need to set up a Data Gateway.