Tried creating a dummy field with the same value on all rows. Could then use a Table.NestedJoin function as opposed to the Table.ExpandTableColumn. Didn’t help.
Tried Left outer Join as well as Full Outer Join. Didn’t help.
Tried removing all the relationships between all of the tables. Didn’t help.
The problem definitely seems due to the fact that the first merge of Date and Areas table retains some sort of link to the SQL Server source. Then when add in the JobTypeGroup table, it needs to refer to both OneDrive and SQL Server. And it doesn’t like it.
As I say above, I hope that the solution is just for IT to check the box to allow our SQL Gateway to allow cloud sources to refresh through the gateway.
Our IT Dept has just changed the Gateway and it has messed everything up completely. My colleague and I now can't even publish a report from Desktop to Service. Once this is sorted out, I will try again.
I am sure that this SHOULD be the solution. Unfortunately, my company's Gateway DOES have this option ticked but I still can't merge data from both Excel files (stored in SharePoint) and our SQL Server database (that has the Gateway). I have got my IT Dept. looking into it but they are confused.