Dataflow: On-Prem execution not supported for entity - Can't Merge Two Tables with Dataflow?

[chrisman2k6]

I have seen some posts on here about this issue, but nothing really seems to answer the question. Without going into too many details, I have an on-prem SQL table that I connect to using an on-prem Gateway that has many tables which all reference each other. For example, I have a Sales table that just has sales data, so if I want to see attached sales reps (Users) or purchasing info (Purchase Order), I need to connect to the other tables. I also would still like to keep the original tables, as they often have way more columns than I want to pull into the Sales table, but they are columns that get used for other purposes.

Is there any way to Merge data in a Dataflow between two tables that are both on-prem? If I choose to "not load" these tables (unclick the Enable Load option), can I add them back as separate tables that are NOT used for merging, then just keep the "merge tables" unloaded? What would be the best approach to accomplish these merges? I have been doing them through Power BI Desktop for 2 years now without issues, and I even have the table refreshable on the web using the on-prem Gateway, but I can't get the same tables to refresh with Dataflows. I want to switch to Dataflows because the data has refreshed MUCH faster than it does for me any other way (an Activity table that takes me 2+ hours to refresh via Desktop is done in less than 10 minutes with Dataflows), but this seems like a massive limitation, or I am just missing something obvious. I am open to any and all suggestions, thanks for taking the time to read my questions!

[v-kkf-msft]

Hi @chrisman2k6 ,

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @chrisman2k6 ,

Please see if the document is helpful:

Merging tables in Power BI dataflows with PRO subscription 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[chrisman2k6]

Sorry for the  delay, thank you for your reply Winniz! I was already aware of the ability to "not load" the merge tables, though I didn't know that I could make these Dataflows without BI Premium. That being said, I have played around more with the Dataflows I am building, and I have discovered a few things:

1.) When I convert my Dataflow to make all "connected" tables NOT load, I am able to get my On-Prem tables to load. However, this still doesn't answer the question as to why I can't merge tables that are On-Prem, because I DO currently have a Premium license, and I merge tables in another Dataflow that is connected to our CRM (Dynamics 365) without issue. Is there NO way to merge On-Prem tables without disabling load on the connected tables?

2.) I liked the idea of converting things since if I have to avoid merging tables anyway, why even bother with the Premium license. However, the same Dataflows when run in a Pro environment vs Premium are MUCH SLOWER. I can refresh with a Premium workspace in under 15 minutes, but the same Dataflow takes 1 to 2 hours when run under Pro. The Dataflow is EXACTLY the same, I literally pulled a JSON file and used it to create the Pro version, it just seems like the Pro version is significantly slower. Could this be beause I don't get the Enhanced Computation Engine when I am on Pro? I don't mind paying for Premium, it just feels like I should be able to do more with my On-Prem tables with the Premium license.

3.) My idea of just duplicating tables so I could use one for a merge that DOESN'T load, then have one that isn't used for a merge but DOES load seems to work, though it of course slows down the overall Dataflow. Wish I had a workaround, but I am guessing with On-Prem that I am just stuck, unless I am missing something.

All in all, I am fine with the current setup, more just disappointed that we are going to pay double the licensing fees for BI just to get the system to refresh faster. It is much faster, so I can't complain that much, but I just wish someone could explain the limitation on merging On-Prem tables. Is there a real reason this can't happen from a technical standpoint, or is it just not supported?

[v-kkf-msft · Accepted solution]

Hi @chrisman2k6 ,

If you only want to use the Pro license, you can try to copy the original table. Use the copied table to merge, which will not affect the loading of the original table.

Referencing: Workaround for Computed Entity in Power BI Pro: Dataflow in Power BI 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[chrisman2k6]

Thanks for the response Winniz! That article covered what I had to do to make our On-Prem tables work with Dataflow, but again, if I use a Pro License / Environment for the refresh, it takes HOURS vs only take 10 to 15 minutes through Premium. I think my question has been as answered as it will be at this point, I will make a new post if I have further Dataflow questions or issues. Happy Friday! 🙂