Have you ever considered exporting your entire Power Query Editor project as a single object? Have you thought about what benefits this would bring for you? Things such as version control and team development can be on the horizon, bringing the ability to migrate between tools and services easily. Fortunately, such functionality exists, called the Power Query Template. In this article and video, I will explain what this is, how it works, and the importance of such a feature.
Video
Introduction
Power Query has been the data transformation engine in Microsoft services (such as Excel and Power BI) for many years. However, until recently, there has been no way to save the Power Query work as a standalone project. If you wanted to save the Power Query project part of the Excel file, you had to save the Excel file itself (which included everything in the file, not just the Power Query part of it). If you wanted to save the Power Query part of the Power BI project, you had to save the Power BI file itself. However, that has changed now. With the introduction of Power Query Templates, you can save the project in a single file and retrieve it when needed.
What is the Power Query Template?
Power Query Template is the content of Power Query Editor exported into a single project file. The Power Query Template includes all the queries (or you may call them tables) and the steps in every query. The parameters, the functions, and every other part of the Power Query Editor related to your project.
The Power Query Template is a file with an extension of PQT. This file includes some parts of the Power Query Editor work as below;
What is included
- all the queries
- functions
- parameters
What is not included
- Refresh settings and history.
- connection credentials
- Dataflow IDs
Generally, anything outside of the Power Query Editor (such as refresh history) won’t be exported into the Power Query Template.
What platform currently supports the Template
- Microsoft Fabric Dataflows Gen 2 (Export and Import)
- Excel (Export, private preview only)
- Power BI Dataflow Gen 1 (Export only)
- Power Platform Dataflow (Export and Import)
Although the feature is not available yet at every service and platform, I believe this will soon change, and you will be able to export and import templates everywhere that the Power Query Editor is embedded.
Exporting into a template
You can create the Power Query Template in Dataflows by clicking on Export template in the Power Query editor online.
Once you select that, you will be asked to enter the name and description for the template file.
This will then download the PQT file, which is the template file.
If you are in the Excel environment, you can export the template by clicking on the File and Export template from the Power Query Editor. Note that this function is only available for a private preview group of Excel at the time of writing this article.
Importing a template
The PQT file can be imported easily. One of the places that you can import it is in Dataflow Gen2. When you create a new Dataflow Gen2, one of the options is to import a template.
When the Dataflow is generated from the template, it will have all the codes, queries, parameters, and functions added in a few seconds. You will only need to set up the credentials for connections.
You would also need to publish and set up the schedule refresh. However, those are the easy parts. Power Query Template moved the most important parts of your work to the new Dataflow.
Structure of template
The Power Query Template file is a file with an extension of PQT. This file is a zipped file, which, when you unzip it, will include the below files;
These files include entities and some metadata about the queries. The most important is the MashupDocument.pq, which has the entire M script.
One giant leap
At first glance, saving the Power Query Editor’s work in a file might not look like a big deal. However, I have a different opinion on that. I think this is the one giant leap in the Power Query world. This opens Power Query to enterprise development, migration, and software development lifecycle.
Migration
You can easily now migrate your Power Query work from one environment to another. Currently, this can be done from Dataflow Gen1 to Dataflow Gen2. But later, this can be done from Excel to Dataflow and Power BI Desktop to Dataflow or vice versa. Power Query Migration can be done quickly without much extra effort.
Version Control
When you can save the Power Query work as a file, it also leads to being able to version control it in Git or other version control platforms. Although this is not possible at the time of writing this article, I’m sure this will be on the horizon.
Power Query for Enterprise: Expect more from the template
Power Query has always been a popular data transformation engine for self-service and citizen data analysts. However, with the recent improvements, I can see that Power Query is much more accessible in an enterprise environment. With Microsoft Fabric‘s Data Pipeline, you can include Dataflow as part of a larger control flow of execution for the data integration. With the Power Query Template, you can save the Power Query work, migrate it between platforms, and version control it in the future. You can see where Microsoft is going with the Power Query, which is the right move. Power Query is an easy-to-use and powerful transformation engine, so putting other components around it to make it usable in every environment makes sense.
Summary
Power Query Template can save the Power Query work as a single file. The Power Query Template can be easily used in migrating Power Query work from Dataflow Gen1 to Dataflow Gen2 (or, in other words, migrate from Power BI Dataflow to Fabric Dataflow). Although this feature is not widespread across all platforms yet, I believe you will have this soon in every place that Power Query Editor is embedded. This will make migrations easier and bring enterprise features on the horizon, such as version control.
In my opinion, some features might not be complicated (compared to query folding, etc.) but can significantly impact the tool’s usage and adoption. I believe the Power Query Template is one of those features. Perhaps one of the best features that happened to Power Query for years, I see this as One Giant Leap that would make a BIG difference in the future. What do you think? Share your thoughts in the comments below.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.