SQL Query Syntax issue

[mike_asplin]

Hi. In the past I have been handed sql queries to use in Power Query.   The structure of these is to specify the DB at the top of the query and then only reference the table name through out

USE

[CURRYS_DW_REPORTING]

GO

WITH CTE_NTT AS

(

SELECT              *

FROM [dbo].[Fact_empower_NotificationTrackingTasks]

This errors when I put it in PQ as follows

DataSource.Error: Microsoft SQL: Incorrect syntax near 'GO'.
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 ','.
Details:
    DataSourceKind=SQL
    DataSourcePath=prd-so-dw2.iia-cloud.com;master
    Message=Incorrect syntax near 'GO'.
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.

I have found if I make the table reference explicit it stops erroring and previously this seems to have fixed it e.g. this

WITH CTE_NTT AS

(

SELECT              *

FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]

Today I have some new queries and these changes dont work. it doesnt error, but just sitting waiting of the DB to respond.

My question is why cant PQ handle the USE type query or can it, but I needed to do something else for it to work?

Any advice appreciated

[Zanqueta]

Hi @mike_asplin , thanks for let me know.

 If this answer resolved your issue, please, give a kudo and mark it as correct to help other members of the community.

[mike_asplin]

sorry it isnt loading. it doesnt show an error but never loads. Is my code below correct?

Thanks

[Zanqueta]

Hi @mike_asplin,

Please provide us with a screenshot of your error.

Perhaps your query is too heavy and is being rejected by the server. Try restricting the data a little using the where command to filter a single day or a single customer, for example.

[mike_asplin]

Sorry there is no error just never loads. I was handed this query by a company a do work for that they use on Server manager because they want the same data in their power BI reports. I have very limited SQL knowledge and usually can just take their query and drop it into Power Query by removing the USE and Go and fully qulaifying the table references. I dont know what CTE means so is that also something PQ cant understand?

[BA_Pete]

CTE is 'Common Table Expression' which is basically just a way of building a temporary table in your SQL query that can be used later in the query (think of it like a table variable in DAX) and is perfectly fine to use in a PQ query.

The real question here is why you're so fixed on using the SQL query at all. Assuming your SQL is basically as simple as you've exampled so far, and that you're querying an SQL Server DB, you can fold all this to the server using M code alone.

Pete

[mike_asplin]

sorry the SQL is way longer than I sampled, but the rest of it looks conventional .  I have amended what i was given by removing USE and Go, fully defining the tables and putting a ; in front of WITH as suggested by zanqueta001

That fixed the errors, but it doesnt retrun anything after 30 minutes 

Revised SQL 

[Zanqueta · Accepted solution]

Yes, your code is quite extensive and seems somewhat complex in terms of many case when statements, but we can leave that for another time. As a suggestion, you could talk to your IT team about generating a table in the database where you can consult these results directly. In the meantime, to validate the connection, I rewrote the code with a few minor adjustments and filtering only one date.

;WITH CTE_NTT AS (
    -- CTE for selecting notification tracking tasks
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
    WHERE NotificationTypeLookupID IN (
        '25'  -- TaskRespondWorkflowInactiveNotification
        ,'14'  -- TaskRespondToPermanentlyLockedCase
        ,'13'  -- TaskRespondToSilentNotification
        ,'12'  -- Task (Review Doc Service Document)
        ,'9'   -- TaskRespondToCommunication
        ,'8'   -- TaskCompleteParkedCall
        ,'7'   -- WorkflowLockedCase
    )
    AND CONVERT(DATE, TaskCreatedDateTime) = '2023-01-01' -- Filter for a single day
),
CTE_CASES AS (
    -- CTE to gather information related to cases
    SELECT
        CTR.CaseTrackingRecordID,
        T.TopicID,
        T.TopicTitle,
        CTR.RowStartDate,
        CTR.RowEndDate,
        OWN.Forename + ' ' + OWN.Surname AS CaseOwner,
        OWN.EmailAddress AS CaseOwnerEmailAddress,
        ADV.Forename + ' ' + ADV.Surname AS CaseAdviser,
        ISNULL(ADV.EmailAddress, 'unknown.unknown@adviserplus.com') AS CaseAdviserEmailAddress,
        W_STAGE.WorkflowStageTitle AS WorkflowStage,
        W_STEP.WorkflowStepTitle AS WorkflowStep
    FROM [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseTrackingRecord] AS CTR
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseStatus] AS CS
        ON CTR.CaseStatusKey = CS.CaseStatusKey
        AND CS.IsValidCase = 1
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS EMP
        ON CTR.EmployeeUserKey = EMP.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN
        ON CTR.OwnerUserKey = OWN.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS ADV
        ON CTR.CurrentAdviserKey = ADV.empowerUserKey
        AND ADV.IsTestAccount = 0
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[dim_empower_topic] AS T
        ON CTR.TopicKey = T.TopicKey
        AND T.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStage] AS W_STAGE
        ON CTR.WorkflowStageKey = W_STAGE.WorkflowStageKey
        AND W_STAGE.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStep] AS W_STEP
        ON CTR.WorkflowStepKey = W_STEP.WorkflowStepKey
        AND W_STEP.RowIsCurrent = 'Y'
)
SELECT DISTINCT
    CASE 
        WHEN NTT.NotificationTypeLookupID = 9 AND CTC.CommunicationSubject = 'Request for Help' THEN 'Help Request'
        WHEN NTT.NotificationTypeLookupID = 9 THEN 'Response Required Case Note'
        WHEN NT.NotificationType = 'TaskRespondToPermanentlyLockedCase' THEN 'Permanently Locked Case'
        WHEN NT.NotificationType = 'TaskRespondToSilentNotification' THEN 'Silent Notification'
        WHEN NT.NotificationType = 'Task (Review Doc Service Document)' THEN 'Doc Review'
        WHEN NT.NotificationType IN ('Task (Complete Parked Call)', 'TaskCompleteParkedCall') THEN 'Parked Call'
        WHEN NT.NotificationType = 'WorkflowLockedCase' THEN 'Workflow Locked Case'
        WHEN NT.NotificationType = 'TaskRespondWorkflowInactiveNotification' THEN 'Inactive Notification'
    END AS [Contact Type],
    CONVERT(DATE, NTT.TaskCreatedDateTime) AS [Contact Date],
    CONVERT(VARCHAR(8), NTT.TaskCreatedDateTime, 108) AS [Contact Time],
    NTTDT.CaseTrackingRecordID AS [Case Number],
    CASES.TopicTitle AS [Topic Title],
    CASES.WorkflowStage AS [Workflow Stage],
    CASES.WorkflowStep AS [Workflow Step],
    NTT.NotificationTrackingTaskID AS [Contact ID],
    CASES.CaseAdviser AS [Adviser],
    CASES.CaseAdviserEmailAddress AS [EMAIL]
FROM CTE_NTT AS NTT WITH (NOLOCK)
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_NotificationType] AS NT WITH (NOLOCK)
    ON NT.NotificationTypeID = NTT.NotificationTypeLookupID
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_Date] AS DD WITH (NOLOCK)
    ON NTT.TaskCreatedDateKey = DD.DateKey
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationDistributions] AS NTTDT
    ON NTTDT.NotificationDistributionID = NTT.NotificationDistributionID
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_CaseTrackingCommunication] AS CTC WITH (NOLOCK)
    ON CTC.CaseTrackingRecordID = NTTDT.CaseTrackingRecordID
    AND ISNULL(CTC.IsDraft, 0) = 0
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN WITH (NOLOCK)
    ON OWN.UserID = CTC.SenderUserID
    AND OWN.RowIsCurrent = 'Y'
INNER JOIN CTE_CASES AS CASES
    ON NTTDT.CaseTrackingRecordID = CASES.CaseTrackingRecordID
    AND NTT.TaskCreatedDateTime BETWEEN CASES.RowStartDate AND CASES.RowEndDate
    AND ISNULL(RIGHT(CASES.CaseAdviserEmailAddress, 15), '') LIKE 'adviserplus.com'
WHERE ISNULL(OWN.EmailAddress, '') NOT LIKE '%adviserplus%'

See if that works.

Your original query:

;WITH CTE_NTT AS (
    -- CTE for selecting notification tracking tasks
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
    WHERE NotificationTypeLookupID IN (
        '25'  -- TaskRespondWorkflowInactiveNotification
        ,'14'  -- TaskRespondToPermanentlyLockedCase
        ,'13'  -- TaskRespondToSilentNotification
        ,'12'  -- Task (Review Doc Service Document)
        ,'9'   -- TaskRespondToCommunication
        ,'8'   -- TaskCompleteParkedCall
        ,'7'   -- WorkflowLockedCase
    )
    AND CONVERT(DATE, TaskCreatedDateTime) BETWEEN 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -25, CONVERT(DATE, GETDATE() - 1))), 0) 
        AND CONVERT(DATE, GETDATE() - 1)
),
CTE_CASES AS (
    -- CTE to gather information related to cases
    SELECT
        CTR.CaseTrackingRecordID,
        T.TopicID,
        T.TopicTitle,
        CTR.RowStartDate,
        CTR.RowEndDate,
        OWN.Forename + ' ' + OWN.Surname AS CaseOwner,
        OWN.EmailAddress AS CaseOwnerEmailAddress,
        ADV.Forename + ' ' + ADV.Surname AS CaseAdviser,
        ISNULL(ADV.EmailAddress, 'unknown.unknown@adviserplus.com') AS CaseAdviserEmailAddress,
        W_STAGE.WorkflowStageTitle AS WorkflowStage,
        W_STEP.WorkflowStepTitle AS WorkflowStep
    FROM [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseTrackingRecord] AS CTR
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseStatus] AS CS
        ON CTR.CaseStatusKey = CS.CaseStatusKey
        AND CS.IsValidCase = 1
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS EMP
        ON CTR.EmployeeUserKey = EMP.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN
        ON CTR.OwnerUserKey = OWN.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS ADV
        ON CTR.CurrentAdviserKey = ADV.empowerUserKey
        AND ADV.IsTestAccount = 0
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[dim_empower_topic] AS T
        ON CTR.TopicKey = T.TopicKey
        AND T.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStage] AS W_STAGE
        ON CTR.WorkflowStageKey = W_STAGE.WorkflowStageKey
        AND W_STAGE.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStep] AS W_STEP
        ON CTR.WorkflowStepKey = W_STEP.WorkflowStepKey
        AND W_STEP.RowIsCurrent = 'Y'
)
SELECT DISTINCT
    CASE 
        WHEN NTT.NotificationTypeLookupID = 9 AND CTC.CommunicationSubject = 'Request for Help' THEN 'Help Request'
        WHEN NTT.NotificationTypeLookupID = 9 THEN 'Response Required Case Note'
        WHEN NT.NotificationType = 'TaskRespondToPermanentlyLockedCase' THEN 'Permanently Locked Case'
        WHEN NT.NotificationType = 'TaskRespondToSilentNotification' THEN 'Silent Notification'
        WHEN NT.NotificationType = 'Task (Review Doc Service Document)' THEN 'Doc Review'
        WHEN NT.NotificationType IN ('Task (Complete Parked Call)', 'TaskCompleteParkedCall') THEN 'Parked Call'
        WHEN NT.NotificationType = 'WorkflowLockedCase' THEN 'Workflow Locked Case'
        WHEN NT.NotificationType = 'TaskRespondWorkflowInactiveNotification' THEN 'Inactive Notification'
    END AS [Contact Type],
    CONVERT(DATE, NTT.TaskCreatedDateTime) AS [Contact Date],
    CONVERT(VARCHAR(8), NTT.TaskCreatedDateTime, 108) AS [Contact Time],
    NTTDT.CaseTrackingRecordID AS [Case Number],
    CASES.TopicTitle AS [Topic Title],
    CASES.WorkflowStage AS [Workflow Stage],
    CASES.WorkflowStep AS [Workflow Step],
    NTT.NotificationTrackingTaskID AS [Contact ID],
    CASES.CaseAdviser AS [Adviser],
    CASES.CaseAdviserEmailAddress AS [EMAIL]
    --,'Test McTestface' AS [Client]
FROM CTE_NTT AS NTT WITH (NOLOCK)
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_NotificationType] AS NT WITH (NOLOCK)
    ON NT.NotificationTypeID = NTT.NotificationTypeLookupID
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_Date] AS DD WITH (NOLOCK)
    ON NTT.TaskCreatedDateKey = DD.DateKey
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationDistributions] AS NTTDT
    ON NTTDT.NotificationDistributionID = NTT.NotificationDistributionID
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_CaseTrackingCommunication] AS CTC WITH (NOLOCK)
    ON CTC.CaseTrackingRecordID = NTTDT.CaseTrackingRecordID
    AND ISNULL(CTC.IsDraft, 0) = 0
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN WITH (NOLOCK)
    ON OWN.UserID = CTC.SenderUserID
    AND OWN.RowIsCurrent = 'Y'
INNER JOIN CTE_CASES AS CASES
    ON NTTDT.CaseTrackingRecordID = CASES.CaseTrackingRecordID
    AND NTT.TaskCreatedDateTime BETWEEN CASES.RowStartDate AND CASES.RowEndDate
    AND ISNULL(RIGHT(CASES.CaseAdviserEmailAddress, 15), '') LIKE 'adviserplus.com'
    -- This case wasn't assigned to a Network Rail_Empower adviser when note/request was sent
WHERE ISNULL(OWN.EmailAddress, '') NOT LIKE '%adviserplus%'

[mike_asplin]

The one day version works hooray. I'm just trying removing the date filter and see if still fails.  Maybe that is the only bit that is the issue just too complex a query.  

is that what you would expect that it runs ok in serever management locally and could then fail in Power Query?  As you say creating a table on the server I can query seems obvious solution. 

[mike_asplin]

I can get it to load 2025 data so obviously just too much data.  Be good know what else you changed so I can handle this in future. really appreciate your help. 

[taavi]

That is really appreciated. As i said thisiis a query they currently run so wanted it replicated exactly in PBI. 

Apart from the single date can you tell me what else you changed for my education? 

[BA_Pete]

No problem. Just thought folding it using M might a) generate a more efficient query/query plan and b) make it much easier to identify exactly which part of the query was causing the delay in processing.

There's already quite a few people on this thread trying to help with getting the native query to work, so I'll drop out now.

Have a nice weekend 🙂

Pete

[tayloramy]

Hi @mike_asplin, 

You can't use SQL in the Power Query editor - only M code will work. 

Use the Get Data menu to select whatever type of database you are using, and then when prompted for the details you can use teh SQL query there. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[mike_asplin]

That's not actually true you just wrap the SQL like this

Source = Sql.Database("PRD-SO-DW1.iia-cloud.com", "master", [Query="

blah blah

"])

I believe you can tuse CTE but I have no idea what that means. basically need to translate form code that works in Server manager to the equivalent that works on Power Query.  my SqL is pretty rubbish 

[mike_asplin]

Not sure i've got this quite right, but thnaks ofr help

Original query

USE
[CURRYS_DW_REPORTING]	

GO

WITH CTE_NTT AS
(
SELECT	*

FROM [dbo].[Fact_empower_NotificationTrackingTasks]
WHERE NotificationTypeLookupID IN	('25'	-- TaskRespondWorkflowinactiveNotifiaction
									,'14'	-- TaskRespondToPermanentlyLockedCase
									,'13'	-- TaskRespondToSilentNotification
									,'12'	-- Task (Review Doc Service Document)
									,'9'	-- TaskRespondToCommunication
									,'8'	-- TaskCompleteParkedCall
									,'7'	-- WorkflowLockedCase
									)
	AND CONVERT(DATE, TaskCreatedDateTime)
		BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -25, CONVERT(DATE, GETDATE()-1))), 0) AND CONVERT(DATE, GETDATE()-1)
),

editted query

;WITH CTE_NTT AS

(
SELECT	*

FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]

WHERE NotificationTypeLookupID IN	('25'	-- TaskRespondWorkflowinactiveNotifiaction
									,'14'	-- TaskRespondToPermanentlyLockedCase
									,'13'	-- TaskRespondToSilentNotification
									,'12'	-- Task (Review Doc Service Document)
									,'9'	-- TaskRespondToCommunication
									,'8'	-- TaskCompleteParkedCall
									,'7'	-- WorkflowLockedCase
									)
	AND CONVERT(DATE, TaskCreatedDateTime)
		BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -25, CONVERT(DATE, GETDATE()-1))), 0) AND CONVERT(DATE, GETDATE()-1)
),

Just sitting spinning its wheels 

[Zanqueta]

Power Query does not support SQL Server commands such as USE and GO. These are intended for SQL Server Management Studio (SSMS) and are not valid within the Power BI query engine. Additionally, when using Common Table Expressions (CTEs), Power Query requires a semicolon (;) before the WITH clause to ensure proper syntax.

---

Recommended Approach

To make your SQL query compatible with Power Query:

1. Remove USE and GO
   These are not recognised by Power Query and will result in syntax errors.
2. Add a semicolon before WITH
   This is required to correctly initiate a CTE.
3. Use fully qualified table names
   Even if the database is defined in the connection, explicitly referencing it can help avoid ambiguity.

;WITH CTE_NTT AS (
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
)
SELECT *
FROM CTE_NTT

f you would like assistance adapting one of your new queries for Power Query, feel free to share it here.

✅ If this answer resolved your issue, please mark it as correct to help other members of the community.