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

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