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;
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 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
2 thoughts on “Reference from Another Query in Dataflow – No Premium Needed – Power Platform Dataflows”
Hi Reza, how much can we trust this to be a possibility in the future? are you sure this won’t change in one o two months and I will need premium then?
Just to be sure before going to a solution like this in production.
There is no Premium license for PowerApss these days.
I do not know if there will be any in the future. But my guess is that this feature will remain possible in the existing license pack anyway.