That title got your attention, didn’t it?
Before we move on, no, you cannot create and publish dataflow entities from Power BI Desktop today. Creating dataflows is a task you need to perform in the browser. But you can build your queries in Power BI Desktop if that is your preferred query authoring tool. Here’s how.
- Create a query or set of queries in Power BI Desktop.
- Copy the query script(s) from the Power BI Desktop query editor, and paste it into a “Blank query” in Power Query Online.
- Rename the new queries to match your desired entity names, being careful to match the names of the source queries if there are any references between them.
- If necessary, disable the “load” option for queries that only implement shared logic and should not be persisted in the dataflow’s CDM folder.
Some of you may be asking “but why would I want to do this, when there’s already an awesome query authoring experience in the browser?”
Good question! There are three reasons why I will often use this technique:
- I prefer rich, non-browser-based editing tools, and Power BI Desktop has a polished and refined UX.
- The online editor doesn’t have all of the transformations in its UI compared to Power BI Desktop.
- The online editor doesn’t have all supported connectors exposed in the UI.
Each of these points relates to the the maturity of Power BI Desktop as a tool, as opposed to the relatively new Power Query Online. Power Query Online is part of the dataflows preview and is continuing to improve and expand in functionality, but Power BI Desktop has been generally available for years.
And Although I didn’t realize it until I started writing this post, Power BI Desktop actually has features that make this scenario easier than expected. Let’s look at this example. Here are the queries I’m starting with:
In the PBIX file I have three sets of queries, organized by folder:
- A query that references the one data source that is used in the other queries, so I can change the connection information in one place and have everything else update.
- Three queries that each contain minimally-transformed data from the data source, and which are not loaded into the data model.
- Two queries that are loaded into the data model and which are used directly for reporting.
This is a common pattern for my PBIX files. I don’t know if it counts as a best practice (especially now that Power BI has better support for parameterization than it did when I started doing things this way) but it works for me, and nicely illustrates the approach I’m following.
To move this query logic out of my PBIX file and into a dataflow definition, I first need to copy the query scripts. Power BI Desktop makes this incredibly easy. When I right-click on any folder and choose “copy”, Power BI Desktop places the scripts for all queries in the workbook – including the query names as comments – on the clipboard.
Now that I have all of the query scripts in a text editor, I can get started by creating a new dataflow, and selecting “Blank query” for my first entity.
After I paste in the query, I will un-select the “Enable load” option, and will paste in the query name as well.
Once this is done, I can repeat the process by selecting “Get data” option in the query editor, and choosing “Blank query” for each additional query.
After I repeat this process for each remaining query, my dataflow will look something like this.
And if I want to, I can even add groups to organize the queries.
This looks a lot like where we started, which is both a good thing and a bad thing. The good side is that it demonstrates how we can use a more mature and authoring experience for our interactive query development. The bad side is that it introduces additional steps into our workflow.
I expect the integration between dataflows and Desktop to only get better over time, but for today, there’s still an easy path if you want to use Power BI Desktop to author your dataflow entities.
As a closing word of caution, please be aware that not all data sources and functions that work in Power BI Desktop will work in dataflows. If you’re using a data source for the first time, or are using a new function, you’ll probably want to test things early to avoid a potentially unpleasant surprise later on.
 I’ve mentioned this technique in a few other posts, but I’ve heard a bunch of questions in recent days that makes me believe that the word isn’t getting out. Hopefully this post will help.
 I’m writing this post from the WordPress Windows app – even though the app offers nothing that the web editor does not, and actually appears to be the thinnest of wrappers around the web editor.
 And they all relate to the fact that Power BI Desktop is just so awesome, nothing compares to it, and although the Power Query Online editor is useful and good, it hasn’t had a team making it better every month, year after year.
 Please remember that this is my personal blog, and that even though I’m a member of the Power BI team, I’m not working on either dataflows or Desktop, so what I expect and what will actually happen aren’t always well aligned.
 Like Html.Table, which is not yet supported in dataflows. Darn it.