Forum Discussion

tempranello's avatar
tempranello
Icon for Advocate I rankAdvocate I
10 years ago
Solved

DirectQuery append

Hello there   I understand that Power BI Desktop currently provides limited functionality when using DirectQuery, but I'm having trouble establishing if I've bumped into the limit of its capabiliti...
  • HarrisMalik's avatar
    HarrisMalik
    10 years ago

    tempranello I dont understand why you are getting the error when you use your query with unions. I have tested the following query

     

    SELECT A, B, C
FROM dbo.TABLE1

UNION 

SELECT A, B, C
FROM dbo.TABLE2

UNION

SELECT A, B, C
FROM dbo.TABLE3

    It is working fine.

     

    Steps:

    1. Get data -> SQL Server

    2. Give SQL Server name in first field

    3. Give Database name in second field 

    4. Open SQL Statement and paste the union query

    5. Click load and select DirectQuery option

     

     

HarrisMalik's avatar
HarrisMalik
Icon for Continued Contributor rankContinued Contributor
10 years ago

tempranello I dont understand why you are getting the error when you use your query with unions. I have tested the following query

 

SELECT A, B, C
FROM dbo.TABLE1

UNION 

SELECT A, B, C
FROM dbo.TABLE2

UNION

SELECT A, B, C
FROM dbo.TABLE3

It is working fine.

 

Steps:

1. Get data -> SQL Server

2. Give SQL Server name in first field

3. Give Database name in second field 

4. Open SQL Statement and paste the union query

5. Click load and select DirectQuery option

 

 

pqian's avatar
pqian
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

tempranello I was able to append in DirectQuery mode just fine. The resultant query should contain the union operation. 

 

Can you post the full query where it failed to fold (the one that shows Not supported in Direct Query Mode error)? Use View->Advance Query Editor to get the full query text.

  • pqian's avatar
    pqian
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    OK, even though I can append the two tables and successfully fold this to the union query, something else is preventing me from actually loading this into the model. This uncovered an issue that we had in the DirectQuery pipeline which we will fix (hopefully soon). For now, your most viable option is to use the custom SQL query. Make sure you don't add any more steps after the source step with the SQL statement.

    • michaelSBI's avatar
      michaelSBI
      Frequent Visitor
      7 years ago

      Is this fixed as i need to do more merges and therefore i cant use the sql statement...