cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User II
Super User II

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

@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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

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

@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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User II
Super User II

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors