Dataflow Refresh Fail Since it Refers to Another Entity

[Anonymous]

I'm trying to move a dataset up to a dataflow and transferred everything by simply copying the advanced editor and pasting it into blank queries within the dataflow query editor.

After doing this and following login prompts and selecting the on prem gateway for one of the data sources, the preview for each query will refresh and look correct, but when I save and close the dataflow, the refresh itself in the Power BI workspace fails with the following error: 

Error: On-Prem execution not supported for entity '[my data]' since it refers to another entity... RootActivityId =[...]

Any help, even a link to "dos" and "donts" of dataflows would be wonderful.

[Syndicate_Admin]

for what it's worth none of the above suggestions or other help articles resolved my version of this issue.
for me, the issue was that i had created a small manual table in the dataflow directly to join to for the purposes of both filtering and cleaning/mapping some strings in one step (i.e. left join on key columns and bring in only the new "clean" column from this table). apparently the dataflow is not capable of this join between a table in the dataflow and a source from MS SQL Server.
I have other joins to tables based on csvs hosted in sharepoint which work without issue.

just another example of a frustratingly vague and multi-purpose error message and limitation from microsoft in this ecosystem

[PunchBird]

I had the same issue after doing a merge and tested both solutions mentioned above, i.e.:

• I merge two referenced queries -> works fine but then I ended up with 4 queries which I think is inefficient
• I merged the two existing queries and disabled load on the non-merged query that was left -> works fine too

[johanjsc]

Did you find a solution for this?

[joel_gibby_csc]

Long article that helped explain what's going on:

https://radacad.com/workaround-for-computed-entity-in-power-bi-pro-dataflow-in-power-bi

Essentially if the results of the query contain complex data types (lists, tables, records), or refers to linked entities, the intermediate query should be disabled for load. This will also prevent the complex types from being reduced to csv-compatible types like text and number (which most likely can't be performed, resulting in nulls). If you already tried to load the intermediate query, open it in advanced editor and you'll see the "Remove Errors" and related steps that can be then commented out or removed. 

Don't forget to update the last "in" to point back to the original last step.

[arock-well]

After discussing with a support technician, I was told that directly merging queries using on-premises SQL tables was not supported (which was what the error message did hint at). Mostly this was due to performance issues when refreshing the dataflows.

The work-around is to create a Reference to the tables you want to do merges. Do this by right-clicking on the dataflow table and select Reference:

Now I have two referenced entities (Employee (2), Job (2)) based on the original tables (Employee, Job):

When I made my merges on the new referenced tables, and ran the refresh, it completed without errors.

Going forward, I would like Microsoft to do a better job of indicating what the error is actually referring to. I also wish since merging of on-prem SQL tables isn't supported that they would disable those buttons altogether, as it's confusing why it even allows you to do this.

I hope that Microsoft will eventually allow for direct merging of on-prem SQL tables in dataflows so I don't have to create a bunch of references. But hopefully this work-around will help someone in the meantime.

Thanks,

Aaron

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SrinivasDornala]

Thank you for the info, @arock-well !!

[Anonymous]

Thanks for sharing and this is helpful. Creating two referenced tables is critical. Also when merging, select option Merge Queires as New instead of just Merge Queries. When I used default Merge Meries, it still failed for me. 

[L_G]

HI, I'm not be able to refresh my dataflow on Power BI Service. (I have a premium per User licence)

I'm trying to set a dataflow in which I have merged tables from SQL Server.

Even if I can save and close the dataflow, as soon as I try to refresh it manually, I get back the following error:

Error: On-Prem execution not supported for entity ' data ' since it refers to another entity.. RootActivityId = ' data '

I've noticed that this error occurs only if I merge or append two tables, even if the came from the same data source...

In particular i got this error:

Can someone help me with this issue??? 

Thank you in advance

Luca

[dax]

Hi @Anonymous , 

I don't know how you refer to another entity in dataflow, could you please explain this to me in details? Then I will try to test this in my environment. Or you also could try to use linked entity to see whether it work or not, you could refer to service-dataflows-linked-entities (you also  could check the limits in this link) for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arock-well]

I see there is no solution to this post. But, I'm having this exact same issue as @Anonymous and @L_G are having.

I have two tables in a dataflow that I pull in from on-prem SQL Server, Employee and Job. Refreshing the tables is not an issue.

However, once I merge Employee into Job and try to refresh the dataflow I get the following error: 

Error: On-Prem execution not supported for entity '777xxx/Job' since it refers to another entity.. RootActivityId = d9xxx.Param1 = 777xxx/Job Request ID: e0xxx.

When I remove the merge query, the dataflow refreshes as normal again.

What do I need to do to fix this?

Thanks

Aaron

[sxt173]

Did anyone find a solution for this? One "solution" I saw on another thread was to uncheck "Enable Load" which defeats the whole purpose if you can't refresh your data.

And I don't understand the overall issue.. it's 2 SQL tables from the same exact DB. I can merge them in PowerBI and publish with on-prem Gateway. But I can't do the same in a Dataflow?