Forum Discussion

michaeljc70's avatar
michaeljc70
Advocate II
10 years ago

Query with CTE gives error

I used SQL Server and a query that uses a CTE.  It gives me an error "Incorrent syntax near the Keyword 'WITH'. ......."if this is a common table expression......previous statement must be terminated...
Need Help
JimLuo's avatar
JimLuo
New Member
9 years ago

I have the same issue with CTE. It will be much convenient to support CTE.   I use a workaround to save data into excel and import it.  

Mandr0id's avatar
Mandr0id
Advocate I
9 years ago

Same issue with CTE... Someone got it working?

  • Nobby's avatar
    Nobby
    Frequent Visitor
    9 years ago

    It's a shame. I also have this problem. I've removed the CTE but the code is much less readable/maintainable. Ho hum.

  • jimjao's avatar
    jimjao
    New Member
    8 years ago

    Hi all,

     

    Using Power BI Desktop (October 2017), I was able to use CTE query with an OLE DB connection to the SQL Server.

    So, Power BI does support CTE.

     

    Jim Jao

  • geoffwill's avatar
    geoffwill
    Regular Visitor
    9 years ago

    Having the same issue with CTEs, however was able to re-write to use a subquery and it works

  • jimjao's avatar
    jimjao
    New Member
    8 years ago

    Hi all,

     

    Using Power BI Desktop (October 2017), I was able to use CTE query along with an OLE DB connection to the SQL Server.

    This is no longer truth.

     

    Jim Jao

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Did you use Import or Direct Query?

     

    Thanks

     

    B

  • roselilly23's avatar
    roselilly23
    Advocate V
    7 years ago

    I was getting the issue when using Direct Query with custom sql containing CTEs. I just re-wrote the query to have sub queries rather than the CTEs and it works fine now. 

  • MasterCMU's avatar
    MasterCMU
    New Member
    6 years ago

    Yes, this is still correct in Power BI Desktop November 2019 - confirmed it works ONLY through a OLE DB connection to SQL rather than through the SQL connection in Power BI.  I came across the issue with CTE at the final "Apply" stage of Power Query with a Direct Connection and while the preview was working, the Apply and Close would fail with the same message as OP's. Using JimJao's fix with Ole DB I got it to Apply and Update. So it seems to be a bug in the SQL connector.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I can confirm this works with OLE DB in PowerBI Desktop, but unfortunately scheduling an online refresh still seems to be causing problems, so the only way it works is republishing from locally updated results every time, which is still a very inconvenient workaround!