Hello, Office Insiders! I’m Jorge Gabuardi, a Program Manager on the Power Query team. I want to share some exciting updates about how you can easily create Power Platform dataflows from your queries in Excel to keep your data refreshed on the cloud.
Power Platform dataflows
Power Query is a powerful tool for ingesting, transforming, and loading data from different data sources into Excel. We heard from you that you want an easy way to use your Excel queries with cloud-powered dataflows. Therefore, avoiding spending resources or time waiting for long running queries to refresh.
For those who aren’t familiar with Power Platform dataflows, they’re a self-service, cloud-based, data preparation technology. They are authored using Power Query. With dataflows, you no longer have to open files in Excel and wait for data refresh to complete. You can simply consume the output, saving valuable time and computer resources. Additionally, you can configure dataflows to run at scheduled intervals so you can always have the latest data available.
We built an easy way for you to export your Excel queries into a Power Query template. Then, you can use that template to quickly add them into a Power Platform dataflow. This saves you from manually moving queries over. At this stage, you will be able to consume the dataflow’s output from other tools like Power BI Desktop or other Power Platform dataflows. However, this is just the first step in our journey. We’re expecting to bring the Power Platform dataflows connector to Excel in the future, as well.
How it works
To export your queries to a Power Query template
- Open Power Query by clicking on Data > Get Data > Launch Power Query Editor.
- In Power Query Editor, click File > Export template.
- When prompted, enter a template name and description.
- Click OK to save the Power Query template.
Using the Power Query template (.pqt) you can create Power Platform dataflows or collaborate with others in your organization by sharing it or checking it in to source control. In the future, we’ll support ways to import templates directly in Excel as well as in other Power Query hosts such as Power BI Desktop.
To create a Power Platform dataflow using a template:
- Go to https://make.powerapps.com/ and click Data > Dataflows.
- Click the New dataflow arrow, and then click Import template in the list.
- Select the Power Query template you created from your Excel queries.
- In the New Dataflow dialog box, notice that the dataflow name and the template metadata is available. Select the Analytical check box to load the data into a managed instance ADLS Gen2. Learn more about different Power Platform dataflow load destinations.
Your Excel queries will be available in the Power Query – Edit queries window.
NOTE: You may be prompted to enter credentials for the data sources used.
- Configure the refresh schedule as desired and save the dataflow which will trigger an automatic refresh.
After the initial refresh is complete, you will be able to consume the dataflow’s output.
Availability
This feature is available to Insiders running Current Channel (Preview) builds Version 2009 (Build 13328.20210) or later.
We typically release features over some time to ensure that things are working smoothly. This is true for Insiders as well. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as Insiders, have had the opportunity to try them.
Feedback
There are a couple of ways to give feedback and suggestions:
- Click Help > Feedback.
- Add #PowerQuery in your feedback so that we can easily find input about the feature.
Learn more about what other information you should include in your feedback to ensure it’s actionable and reaches the right people. We’re excited to hear from you!
With the Office Insider newsletter, you can get the latest information about Insider features in your inbox once a month.