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.



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.


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.