An on-premises data gateway is a tool for connecting local data and systems to the cloud. Although this example focuses on Power BI, the gateway can also be used with other Power Platform services, including Power Automate and Power Apps. There are two gateway options discussed in this class:
- Standard gateway, which works with multiple Microsoft services
- Personal gateway, which is limited to Power BI
This example uses the standard gateway.
Connect local data to Power BI
With a data gateway connected to Power BI, you can use locally stored data in cloud dataflows and reports. Examples of supported local sources discussed in this class include:
- Excel workbooks
- CSV files
- SQL Server
The example demonstrates connecting to an Excel file on another machine and using its data in a Power BI report.
Install the standard gateway
In Power BI, select Downloads, then Data Gateway. This opens a page where you can download either the standard or personal gateway. For this example, select the standard gateway. Microsoft recommends at least an 8-core processor and 8 GB of RAM for the data gateway. A hardwired network connection is also recommended, rather than Wi-Fi, to help reduce performance issues when syncing data to the cloud. After downloading the installer:
- Choose the installation location.
- Accept the terms of service.
- Select Install.
- Complete the required prompts.
- Sign in with the Microsoft account you want to use to register the gateway.
Register the gateway
If you aren't migrating or restoring an existing gateway, select the option to register a new gateway. Next, give the gateway a name and create a recovery key. The recovery key is used if you need to move the gateway to another machine or add it to a gateway cluster. After registration, the gateway's start screen provides options to check its status, adjust service settings, restart the gateway, view logs, test network ports, manage security settings, and import custom connectors.
Create a Power BI dataflow
In the Power BI service, select New and create a new dataflow. Add a new table and select Excel workbook as the data source. Enter the file path for the Excel workbook stored on the local machine. Under the connection credentials:
- Select Create a new connection.
- Choose the newly registered data gateway.
- Enter the username and password for the Windows user on the machine.
A password must be set for the Windows account. Using an empty password can cause the query to fail without clearly identifying the reason. After entering the credentials, select Next to connect to the file through the gateway and bring the data into Power BI.
Prepare and save your data
Once the data has been connected, you can work with it similarly to other Power Query and dataflow scenarios. For example, you can:
- Rename tables
- Delete columns
- Change data types
When you're finished preparing the data, save and close the dataflow. You can then refresh it to pull in the data from your local source.
Use the dataflow in a Power BI report
Back in Power BI on your local computer, connect to the dataflow as a new source. Select your workspace, dataflow, and table to bring the data into the report. You can then apply the changes and use the new table as the report's data source.
Keep local data updated
One advantage of combining a dataflow with a Power BI data gateway is the ability to reflect changes made to the original local file. For example, if you update the local Excel workbook, you can refresh the dataflow in the Power BI service to pull in the new information. After refreshing the report data, those changes become available in Power BI. The class also demonstrates how to configure an automatic refresh for the dataflow and report. With a Power BI Pro license, new data can be pulled in up to eight times per day.
Bring local and cloud data together
A data gateway provides a way for Microsoft cloud applications to access data stored locally. With Power BI, you can use the gateway to connect local files to dataflows and incorporate that information into your reports. By combining the gateway with dataflow refreshes, you can keep Power BI data aligned with changes made to the underlying local source.
