Reference from Another Query in Dataflow – No Premium Needed – Power Platform Dataflows

I have previously explained one of the benefits of Power Platform dataflows, which was the ability to refresh the data as many times as you want (on a scheduled basis), and the ability to refresh even with the frequency of a minute. Another useful feature of Power Platform dataflows is the ability to have referenced queries. This feature in Power BI dataflows is only limited to Premium (if the original query is enabled load). Let’s see how this feature works in Power Platform dataflows.

Referencing from another query

In Power BI, or let’s say in Power Query, sometimes you need to use another query as a source, and continue some extra steps from that query as a 2nd query. This leads to creating a REFERENCE from that query. I have explained in details in another blog post about what is the reference query and what is the difference of that with a duplicate query.

The ability to reference a query is useful in many scenarios. One of the main use cases of that is when you combine queries together through Merge or Append. for example, in the query dependency diagram below, you can see that the final query is sourced from the other two queries because this is the result of appending those two queries. This means that the Box Office Mojo All Pages has REFERENCED each of the other two queries.

What is Dataflow?

If you are not a dataflow user or never heard of it. It is explained here in my blog article. Dataflows are the Power Query process that runs in cloud independent from the Power BI dataset. This would easily decouple the data transformation layer of your Power BI solution from the rest of it.

Limitation on the REFERENCE in Power BI Dataflows

If you use Power BI dataflows and try to create a reference from another query, and if the original query is enabled load, then you will get into the issue of creating Computed Entity.

Computed Entity is limited only to the Premium license of Power BI.

You can see in the screenshot above that the icon for a computed entity is different from normal entities.

I have explained, however, in another blog article what are workarounds for this process. If you don’t have the Power BI Premium license (let’s say the company you work for, still doesn’t have the budget to allocate for the premium only because of this feature), then you can use the workaround I explained here. Most of the methods explained in that article are considering that you either don’t reference the original query and use the duplicate. Or, alternatively, if you do use the reference, then disable the load of the original query.

So the bottom line is that those are workarounds, not solutions actually. But now we have a real solution for that.

Power Platform Dataflows

Power Platform Dataflows are Power Query processes that you define under Power Apps (or Power Platform) portal and run independently from Power BI.

If you create your dataflow using this process, then you can easily REFERENCE another query, and this won’t create a computed entity. you won’t need a premium account for that.

You can reference another query as below:

After doing all the transformations you need, you can load it to an existing or a new entity in CDS.

and that’s it. It works perfectly fine.

Then you can get data in Power BI Desktop from the result of this dataflow, either using Power Platform Dataflows, or Common Data Services.

How to use the results in Power BI?

You can use the result of dataflows in Power BI (similar to the way that you can use the result of Power BI dataflows in Power BI). You need to Get Data from Power Platform Dataflows.

However, at the time of writing this blog post, this feature is still beta and under development, and might not show the Power Platform dataflows.

Another option is also to get data from the Common Data Service. (Because Power Platform Dataflows stores the data into CDS)

What about Licensing?

For Power Platform dataflows, you don’t need a Power BI license at all, not Premium, not even Pro. You do need, however, to have a Power Apps license. at the time of writing this blog post, there are two options, $10 a month, and $40 a month. Both of these can be cheaper than Power BI Premium if you are a single user (or even a few users). The main difference between the two plans of Power Apps, as I see is the database and file size for CDS;

Summary

Power Platform dataflows will give you the ability to reference from another query, this won’t create a computed entity or even referenced entity. All possible using Power Apps licensing. The result of these dataflows are written in CDS, and as a result, can be used in Power BI Desktop. If your organization is not considering purchasing a premium license for Power BI, and you are desperately looking for using this feature in dataflows, then go ahead and use it this way.

Related Links (Read more)

Reference Vs. Duplicate in Power Query

Merge Vs. Append in Power Query

Power BI Dataflows use cases

Common Data Services

Power Platform Dataflows and the reference frequency

A workaround for Computed Entity in Power BI Pro

What is enabled load and the performance impact of that

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.