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

The article covers how we can configure data gateway in Power BI for On-Premise Excel files. We have covered installation and configuration process of connecting on-premise Excel data souse with Power BI service.

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".

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

Choose Recommended Mode > click Next.

Step 4

It will prompt a message like this.

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

Step 5

Choose a path for installation.

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

Step 6

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

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

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.
Install and Configure Data Gateway for On-Premise Excel Files in Power BI 

Step 9

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

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

Configure Gateway for On-Premise Excel Files(s)

Step 1

From Settings gear, open "Manage gateways".

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

Step 2

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

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

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".
 
 Install and Configure Data Gateway for On-Premise Excel Files in Power BI

Step 4

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

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

Step 5

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

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

Click "Apply".

Step 6

It will show a notification.

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

Step 7

Let’s configure a daily refresh.

Expand the "Schedule Refresh" option.

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

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.