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:
https://learn.microsoft.com/en-us/power-query/new-dataflow-from-template
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:
https://learn.microsoft.com/en-us/fabric/data-factory/move-dataflow-gen1-to-dataflow-gen2
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:
[If you do import this template close to the time of writing this post you may run into this bug from the list of known issues for Fabric Data Factory and Dataflows]
Hopefully we’ll start to see more and more useful Power Query queries and functions shared as template files!
Hello! Thanks for the great content? Is it possible to export the .pqt from Power Query (in PBI Desktop) and then impor that .pqt into the Dataflows (in PBI Service)?
Nice! You could copy-paste all queries to PQ online, but is the template better in some ways – besides the obvious fact, that sharing will be much nicer?
btw, I dont see the export menu item in Excel 365, Build 16.0.16501.20074 ..?
?
I have it in build 16.0.16529.20002
Ah, only available on the insider program, not released yet
The File menu doesn’t include an Import Template command. So we apparently can’t import an exported query to another workbook.
And if we copy and paste a query, we have no option to use custom functions and other lower-level queries that are already in the target workbook. So that forces us to clean up the M-code mess that pasting queries creates.
In short, it looks like Microsoft is trying to make it easy to transition from Excel to other apps, while making it difficult to stay in Excel.
I agree that it would be good to have an Import option too, but it’s definitely not part of a master plan to move people away from Excel I promise!
Chris,
In the past several years you’ve concentrated so much on Power BI that I’m always surprised to see you mention Excel. And my assumption is that when you feature only “Power BI” that Excel users shouldn’t waste their time reading your blog.
In fact, it seems that Excel PQ has had no updates for several years. I’ve never seen PQ mentioned in Excel’s “What’s New?” content, and I haven’t noticed any changes to Excel’s PQ Ribbon. (I’m on the 365 Insider Current Channel.)
So if Microsoft is still investing in PQ for Excel, you folks have been very quiet about it. Or am I missing something?
Charley
I’m sorry to hear that you think Excel has been neglected. As far as Power Query goes, all the updates that have gone into Power BI Desktop have also gone into Excel too – these are almost all new M functions though. Excel PQ also got the ability to create Linked Data Types too. All the work by the Excel PQ team has gone into making PQ work in Excel for the Mac and in Excel Online (there have been a lot of posts about this on the Excel blog) and eventually the web-based version of the Power Query Editor, as seen in Power BI dataflows, will replace the version in Excel and Power BI Desktop so you’ll get things like Schema View there too.
“All the work by the Excel PQ team has gone into making PQ work in Excel for the Mac and in Excel Online”
So they’ve ignored Windows Desktop Excel? That’s what I’m complaining about.
For years, when QuickBooks users have asked about a QB connection for PQ, I’ve sent them to Intuit, CData, and Clearify. And when they ask why Power BI has a QB connector, but not Excel, the only answer I can offer is that Microsoft has chosen to give Excel less DTL power than they give Power BI.
How long has Power BI had a QB connector, Chris? Four years? Six? That’s not neglect; it’s a choice.
Part of the problem may well be a matter of communication, which I know that you’re working hard to improve. But I’ve NEVER seen any mention of Power Query in Excel’s File, Account, What’s New pane.
The tools in Data, Get & Transform Data are supposedly part of Excel. And What’s New probably is Microsoft’s most-visited source for whats-new information about Excel. But (apparently) Excel’s What’s New feature ignores new additions to that part of Excel. (I admit, however, that Excel’s What’s New feature is buggy. On my computer, it’s been stuck on August 2022 until the last update. Now it just displays an error message.)
And also, come to think of it, I’ve never stumbled across a high-level explanation about the Microsoft apps to which Excel can connect, and why we should care.
Ah, well. It is what it is.
Thanks, Chris.
Those are fair points, and I know the Excel PQ team would also like to add the full range of connectors to Excel too. There is a lot of other good stuff for PQ coming to Excel in the future though, I promise.
Sorry are we forgetting elephant in the room python script integration in Microsoft Excel here ? I mean Power BI – Power Query allows python script to run , it would be so cool if Microsoft Excel Desktop version allowed in Power Query the same.