Dataflow Refresh Fail Since it Refers to Another Entity

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.