BUG: SQL query works in Azure Data Studio but isn't accepted in Power BI

[Anonymous]

I think I've found a bug in the way that embedded SQL queries are handled in Power BI.  I'm using Power BI latest (Apr) version.  My SQL query is listed below.  When I go to add the SQL query to a Power BI (via the Home -> SQL Server menu item), the query shows proper results, until I click "Load", then I get this error:

Microsoft SQL: Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.

I have been able to successfully setup SQL queries like this multiple times in Power BI, and I'm using the exact same data source/credentials, so I know that's not the issue.  In my case, I'm connecting to a SQL server that's part of Atlassian's Enterprise Insights product, which is hosted on Azure.

Steps to Repro:

1. Create a new Power BI file.

2. Add a Table visual to the active tab/canvas.

3. Save the file to some name (doesn't matter).

4. Setup your SQL connection -- Azure SQL Server in my case, using standard SSO connectivity.

5. Add the following tables to the Power BI file, using Direct Query:  current_dw.Epic, current_dw.Epic Benefits, current_dw.Portfolio, current_dw.Program, current_dw.Theme, current_dw.User

6. Click Home -> SQL Server.  Enter the name of the SQL server and DB to connect to.  Select Direct Query.

7. Under Advanced Options, paste in the SQL query below.

8. Click Ok.  The next dialog shows the query ran successfully and returned the expected data.

9. Click Load.  Wait a moment.  You get the error.  No error should be shown and it should work.

SQL Query (anonymized):

WITH EpicBenefitsData1 

    AS (SELECT "Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID", 

            Epics.[Custom Text 1] AS "Z Initiative ID", 

            Themes.[Theme Name] AS "Theme Title",

            Epics.[Epic Name] AS "Initiative Name", 

            Users.[Full Name] AS "Initiative Owner Name",

            Epics.[Epic Description] AS "Initiative Description",

            Epics.[Custom Dropdown 3] AS "Initiative Status",

            Epics.[Epic State] AS "Initiative State", 

            Epics.[Developmental Step] AS "Initiative Process Step", 

            Programs.[Program Name] AS "Primary Program",

            Portfolios.[Portfolio Name] AS "Portfolio"

        FROM [current_dw].[Epic Benefits] AS EpicBenefits1 

            INNER JOIN [current_dw].[Epic] AS Epics ON Epics.[Epic ID] = EpicBenefits1.[FK Epic ID]

            INNER JOIN [current_dw].[User] AS Users ON Users.[User ID] = Epics.[FK UserOwner ID]

            INNER JOIN [current_dw].[Program] AS Programs ON Epics.[FK Program ID] = Programs.[Program ID]

            INNER JOIN [current_dw].[Portfolio] AS Portfolios ON Epics.[FK Portfolio ID] = Portfolios.[Portfolio ID]

            INNER JOIN [current_dw].[Theme] AS Themes ON Themes.[Theme ID] = Epics.[FK Theme ID]

        WHERE EpicBenefits1.[Epic Benefits Field Set Name] = 'Default'

        UNION

        SELECT "Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID", 

            Epics.[Custom Text 1], 

            Themes.[Theme Name],

            Epics.[Epic Name],

            Users.[Full Name],

            Epics.[Epic Description],

            Epics.[Custom Dropdown 3],

            Epics.[Epic State], 

            Epics.[Developmental Step], 

            Programs.[Program Name],

            Portfolios.[Portfolio Name]

        FROM [current_dw].[Epic Benefits] AS EpicBenefits2 

            INNER JOIN [current_dw].[Epic] AS Epics ON Epics.[Epic ID] = EpicBenefits2.[FK Epic ID]

            INNER JOIN [current_dw].[User] AS Users ON Users.[User ID] = Epics.[FK UserOwner ID]

            INNER JOIN [current_dw].[Program] AS Programs ON Epics.[FK Program ID] = Programs.[Program ID]

            INNER JOIN [current_dw].[Portfolio] AS Portfolios ON Epics.[FK Portfolio ID] = Portfolios.[Portfolio ID]

            INNER JOIN [current_dw].[Theme] AS Themes ON Themes.[Theme ID] = Epics.[FK Theme ID]

        WHERE EpicBenefits2.[Epic Benefits Field Set Name] = 'Z Lean Business Case')

SELECT

    "Z Initiative ID",

    "FK Epic ID" AS "Initiative ID",

    "Theme Title",

    "Initiative Name",

    "Initiative Owner Name",

    "Initiative Description",

    "Initiative Status",

    "Initiative State", 

    "Initiative Process Step", 

    "Primary Program",

    "Portfolio",

    "Analysis Summary", 

    "Current Situation", 

    "Funding Stage", 

    "Future State & Desired Outcome", 

    "In Scope", 

    "Notes", 

    "Out of Scope",

    "Recommendation & Desired Solution", 

    "Sponsors", 

    "Support Required & Products Affected"

FROM

    EpicBenefitsData1

    PIVOT (MAX(EpicBenefitsData1."Epic Benefits Field Value") 

        FOR EpicBenefitsData1."Epic Benefits Field Name" IN ("Funding Stage", "Future State & Desired Outcome", "Current Situation", 

                                       "Recommendation & Desired Solution", "Support Required & Products Affected",

                                       "Analysis Summary", "Notes", "Sponsors", "In Scope", "Out of Scope"))

        AS EpicBenefitsData4

Not sure where the problem lies here, but since my query works in Azure Data Studio and the first part of the Power BI SQL query import, it must be something on how Power BI handles this in the back end.

[v-yanjiang-msft]

Hi @Anonymous ,

I can't test exactly by the code you provided as I haven't the exactly same database. In my side, it works fine with a simple code. I have some suggestions:

1. Update to the latest version 2.116.843.0 published yesterday from below link:

Download Microsoft Power BI Desktop from Official Microsoft Download Center

2. Test if it can work by the option "Transform" instead of "Load". 

3. Test if a simple code can be loaded sucessfully. If so, I think the issue mostly relates to the code, simplify the code, keep the outermost frame, and fill in little by little to see which step went wrong.

Best regards,

Community Support Team_yanjiang