Power Query: Template Tease

Welcome to our Power Query blog.  Today, I take a sneak peek at a feature that is currently only available on the ‘Office Insider’ program.

Readers who followed along with the series that culminated last week will have noticed that working with a large dataset and involved queries meant that refreshing took a noticeable amount of time before the data from the query was available.

There is some functionality, currently only available to those users on the ‘Office Insider’ program, which allows me to use Microsoft Power Platform, and in particular, Power Apps, to perform regular refreshes.  Microsoft Power Platform uses Power Fx, a low-code language based on Excel, which incorporates an incremental compiler to update formulas while the app is running and without disturbing state. 

By using Power Apps dataflows, I can free up my computer resources, and still have the latest data from my queries available to use in Excel.

There are two [2] steps to achieve this: I need to export my queries from Excel to a Power Query template, and then in Power Apps, I must create a dataflow from that template.

Export queries from Excel to a Power Query template

I start in the Power Query editor, which I can access by double-clicking on any query in the ‘Queries & Connections’ pane, or from the Data tab, using ‘Get Data’ and then ‘Launch Power Query Editor’.

The next step, which is only available to ‘Office Insider’ users, is to access ‘Export Template’ on the File tab in the Power Query editor screen:

I am then prompted for a ‘Template name’, and I may enter a ‘Template description’.  

When I click OK, this will create a PQT file (.pqt) in my chosen location.

Create a Power Apps dataflow from the template

I start by signing in to ‘Power Apps’:

From the ’… More’ option, I choose to pin Dataflows:

On the ‘New dataflow’ dropdown, I can choose to ‘Import template’:

I can then browse to select the Power Query template I created and create a dataflow which will automatically have the same name as my template. Having checked the queries (more on this next week), I can then choose the refresh settings:

Once published, I can view the dataflow:    

We have articles on how to use dataflows in Power BI, beginning with Part 1.  

There is no current date set for when Power Query template creation will be Generally Available, but I look forward to updating you when that happens.  Next time, as promised, I will have a look at Power Query Online functionality available when creating a dataflow.

Come back next time for more ways to use Power Query!