cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors