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
rbbi's avatar
rbbi
Advocate II
9 years ago

your issue and thoughts about it are exactly what I'm facing today. Would be nice if it jsut passed through the query and used the results like it does in the preview. My query also works fine in PowerPivot with a recursive CTE and I was just trying it in Power BI but the same code in Power BI isn't accepted. cheers

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.  

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

  • Mandr0id's avatar
    Mandr0id
    Advocate I
    9 years ago

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

  • 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!