Create an Excel Power Query template

It’s been said that data is always on a journey. The process of moving and shaping data from one source to another is called Extract, Transform, and Load (ETL). Traditionally, this process has been complex, error-prone, and time-consuming.

But now you can export an Excel Power Query to a template file (.pqt). Then you can import this template file into a dataflow, which is a data preparation feature of the Power Platform. Products such as Power Apps and Power BI Desktop use a data flow to specify an output destination and automatically refresh it at regular intervals.

Think of a Power Query template as a portable package that contains data connection, authentication, and transformation information, and that significantly streamlines, automates, and standardizes the process of ETL.

Procedure

Creating and using a Power Query template is a two-step process.

From Excel, export your Power Query to a template file

1. Open Power Query by selecting Data > Get Data > Launch Power Query Editor.

2. In the Power Query Editor, click File > Export Template.

3. In the Export Power Query Template dialog box, enter a template name and optionally a description.

4. To save the template, select Export.

In Power Apps, import the Power Query template file to create a dataflow

1. Go to Power Apps.

2. In the pane on the left, select Data > Dataflows.

3. At the top of the window, select New Dataflow > Import Template.

4. In the Open dialog box, locate the Power Query template file you created, select it, and then select Open.

5. In the New Dataflow dialog box, accept or change the name.

6. Optionally, select Analytical Entities only to load data into Azure Data Lake for analytical purposes and to unlock Al based insights.

Result      The dataflow opens in the Power Query - Edit queries window. You may be prompted to enter credentials for one or more data sources. You can also configure a refresh operation. For more information, see Using incremental refresh with dataflows.