Has anyone successfully used Table.Combine (append) or UNION for a Direct Query report?

[edhans]

We have several Direct Query reports that work fine against an on-prem SQL Server via an Enterprise Gateway cluster.

I was trying to write a new report that would combine 2 tables - posted and unposted transactions - into a single table. It is a bit more involved than that, but you get the idea.

If I load the 2 tables individually, they are in Direct Query mode. If I use Table.Combine in PowerQuery though on those tables and load that, it is in Import mode.

If I join the two tables in the DAX model itself with a UNION() function, that table is in Import mode, even though the two supporting tables are in DQ mode. And when data changes, the two tables update, but visuals based on the UNION() table do not.

I cannot think of anything else to try, but it seems to me that using Table.Combine, which is fully supported in Query Folding, and which PQ gives no indication it is switching to Import mode, doesn't work in DQ mode.

I have the latest desktop, just updated yesterday from the store.

[v-chuncz-msft · Accepted solution]

@edhans ,

You may specify SQL statement in the Advanced options.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=s...

[edhans]

Thanks. That would work, but then the entire model would have to be a single SQL statement. Brutal. Thanks for the suggestion though. It could work.