Earlier this year a new feature was added to Power Query in Excel and Dataflows that very few people noticed: the abilty to export all the queries in the Power Query Editor to a template file. The reason few people noticed it was that, when the feature released, the only place you could import a template file was in a Power Platform dataflow. The documentation for exporting from Excel and importing to a Power Platform dataflow is very detailed and can be found here:
With the release of Fabric you can now import templates into Gen2 Dataflows. This means you can export a template from a Gen1 dataflow and import it into a Gen2 dataflow, which is documented here:
Joining the dots, this also means you can now export a template from Power Query in Excel and import it to a Gen2 dataflow. This makes it easy to develop on your PC before pushing your work up to Fabric. Here’s a quick walkthrough of how to do this.
In Excel Power Query let’s say you have one or more queries in the Power Query Editor:
If you then go to the File menu in the top left corner of the screen you’ll see the option to export to a template:
If you click this menu item, you’ll see a dialog where you can enter a name and description for the template:
Click Export and you can save the template to a .pqt file.
If you then create a new Gen2 Dataflow in Fabric then, in the middle of the screen, you’ll see the option to import from a template:
[Notice also the Export Template option in the top right corner]
If you import the .pqt file created in Excel the queries in it will get created in the dataflow. You may need to re-enter credentials:
Once you’ve done that, the queries run:
Template files are the way the cool kids share their Power Query queries these days and no kid is cooler than my colleague Alex Powers who, I saw on Twitter recently, has created a template file that scrapes all the ideas from the Fabric Ideas site:
Hopefully we’ll start to see more and more useful Power Query queries and functions shared as template files!