cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Trying to get a CTE into a PowerBI report and receive an error.

The query works in SSRS. I am able to preview data in PowerBI. However, when I apply on the powerquery editor, I receive "Microsoft SQL: Incorrect sysntax near the keyword 'Declare'. Incorrect syntax near ')'." I have tried adding in ; to a couple of places then removing. I am not very good at powerbi but everything looks good in SQL. What am I missing?

 

 

 

USE [DWDataMart2];

DECLARE @CR_CancelledId int = 7;
DECLARE @CR_CompletedId int = 9;

-- First CTE
WITH WI_RA AS
(
    SELECT WIActivity.WorkItemDimKey
	   ,WIActivity.[WorkItemContainsActivity_ActivityDimKey]
	   ,CONVERT(int,Review.[DisplayName]) AS 'Order'
	   ,A.[Id]
	   ,A.[Title]
	   ,A_Status.EnumDisplayName AS 'RA Status'
	   ,Review.ReviewerDimKey AS 'Review_ReviewerDimKey'
	   ,Review_AssignedUser.UserDimKey AS 'ReviewerAssigned_UserDimKey'
	   ,Review_VotedByUser.UserDimKey AS 'ReviewerVoted_UserDimKey'
       ,Review_AssignedUser.DisplayName AS 'Reviewer Assigned'
       ,Review.Veto AS 'Has Veto'
       ,Review.MustVote AS 'Must Vote'
       ,Review_VotedByUser.DisplayName AS 'Reviewer Voted'
       ,R_Decision.[Decision]
       ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Review.DecisionDate) AS 'Decision Date'
    FROM [dbo].[ReviewActivityHasReviewerFactvw] HasReviewer (nolock)
    JOIN [dbo].[ActivityDimvw] A (nolock) ON A.ActivityDimKey = HasReviewer.ActivityDimKey
	LEFT JOIN (
				SELECT Enum.[ActivityStatusId] AS 'EnumDimKey'
					   ,DS.[DisplayName] AS 'EnumDisplayName'
					   ,Enum.[ParentId] as 'EnumParentDimKey'
					   FROM [dbo].[ActivityStatusvw] Enum (nolock)
					   JOIN [dbo].[DisplayStringDimvw] DS (nolock) ON DS.[BaseManagedEntityId] = Enum.[EnumTypeId] AND DS.[LanguageCode] = 'ENU'
				) AS A_Status ON A_Status.[EnumDimKey] = A.[Status_ActivityStatusId] 
	JOIN [dbo].[WorkItemContainsActivityFactvw] WIActivity (nolock) ON WIActivity.WorkItemContainsActivity_ActivityDimKey = A.ActivityDimKey
	JOIN [dbo].[ReviewerDimvw] Review (nolock) ON HasReviewer.ReviewActivityHasReviewer_ReviewerDimKey = Review.ReviewerDimKey
	LEFT JOIN (
				SELECT reviewUserFact.[ReviewerDimKey], AssignedReviewer.[UserDimKey], AssignedReviewer.[DisplayName]
				FROM [dbo].[ReviewerIsUserFactvw] reviewUserFact (nolock)
				JOIN [dbo].[UserDimvw] AssignedReviewer (nolock) ON AssignedReviewer.UserDimKey = reviewUserFact.ReviewerIsUser_UserDimKey
			  ) AS Review_AssignedUser ON Review_AssignedUser.ReviewerDimKey = Review.ReviewerDimKey
	LEFT JOIN (
				SELECT votedByFact.[ReviewerDimKey], VotedBy.[UserDimKey], VotedBy.[DisplayName]
				FROM [dbo].[ReviewerVotedByUserFactvw] votedByFact (nolock)
				JOIN [dbo].[UserDimvw] VotedBy (nolock) ON VotedBy.UserDimKey = votedByFact.ReviewerVotedByUser_UserDimKey
              ) AS Review_VotedByUser ON Review_VotedByUser.ReviewerDimKey = Review.ReviewerDimKey
    LEFT JOIN (
				SELECT R_Decision.[ReviewerDecisionId], R_Decision.[ID] AS 'DecisionEnumName', DS.[DisplayName] AS 'Decision'
				FROM [dbo].[ReviewerDecisionvw] R_Decision (nolock) 
				JOIN [dbo].[DisplayStringDimvw] DS (nolock) ON DS.[BaseManagedEntityId] = R_Decision.[EnumTypeId] AND DS.LanguageCode = 'ENU'
			  ) AS R_Decision ON R_Decision.[ReviewerDecisionId] = Review.[Decision_ReviewerDecisionId]
	--Order by not allowed inside a WITH thingy
    --ORDER BY CONVERT(int,Review.[DisplayName]) ASC
)
-- End of First CTE
,
-- Start 2nd CTE
CR_LastFinished AS
(
	SELECT CR_StatusDur.[ChangeRequestDimKey], MAX(CR_StatusDur.[StartDateTime]) as 'FinishedDate'
	from [dbo].[ChangeRequestStatusDurationFactvw] CR_StatusDur (nolock)

	WHERE CR_StatusDur.[ChangeStatusId] IN (@CR_CancelledId, @CR_CompletedId) 
    AND CR_StatusDur.[FinishDateTime] IS NULL

	group by CR_StatusDur.[ChangeRequestDimKey]
)
-- End 2nd CTE
,
-- Start 3rd CTE
WIAA_Latest AS 
(
	SELECT WIAA.[WorkItemDimKey], MAX(WIAA.[CreatedDate]) as 'MaxCreatedDate'
	from [dbo].[WorkItemAssignedToUserFactvw] WIAA (nolock)
	group by WIAA.WorkItemDimKey
)
--End 3rd CTE

--Main Query
SELECT CR.[Id]
	  ,CR.[Title]
	  ,CR.[Description]
	  ,WI_RA.[Id] AS 'RA Id'
	  ,WI_RA.[Reviewer Assigned]
	  ,WI_RA.[Reviewer Voted]
	  ,WI_RA.[Decision]
	  ,WI_RA.[RA Status]
	  ,[Decision Date]
	  ,AssignedTo.[AssignedToUser] AS 'Assigned To'
      ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CR.[CreatedDate]), 'MM/dd/yyyy h:mm tt') AS 'Created Date'
	  ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CR_LastFinished.[FinishedDate]), 'MM/dd/yyyy h:mm tt') AS 'Finished Date'
FROM [dbo].[ChangeRequestDimvw] CR (nolock)
JOIN [dbo].[WorkItemDimvw] WI (nolock) ON WI.[EntityDimKey] = CR.[EntityDimKey]
-- Joining CTE's
LEFT JOIN WI_RA ON WI_RA.[WorkItemDimKey] = WI.[WorkItemDimKey]
LEFT JOIN CR_LastFinished ON CR_LastFinished.[ChangeRequestDimKey] = CR.[ChangeRequestDimKey]
LEFT JOIN WIAA_Latest ON WIAA_Latest.WorkItemDimKey = WI.WorkItemDimKey
-- End Joining CTE's
LEFT JOIN (
			SELECT AssignedToFact.WorkItemDimKey, AssignedToFact.CreatedDate, AssignedToFact.DeletedDate, AssignedToUser.DisplayName AS 'AssignedToUser', AssignedToUser.UserDimKey 
			FROM [dbo].[WorkItemAssignedToUserFactvw] AssignedToFact (nolock)
			JOIN dbo.UserDimvw (nolock) AS AssignedToUser ON AssignedToUser.UserDimKey = AssignedToFact.WorkItemAssignedToUser_UserDimKey
		  ) AS AssignedTo ON AssignedTo.WorkItemDimKey = WI.WorkItemDimKey AND AssignedTo.CreatedDate = WIAA_Latest.MaxCreatedDate
ORDER BY CR.[Id], WI_RA.[Order]

 

 

 

 

1 ACCEPTED SOLUTION

Sorry, but I cannot replicate this issue. I've created a query with a CTE and it works both in preview and when I click apply. The error looks like it is coming from SQL Server. My suggestion would be to run a SQL Profiler trace to capture the actual query that is being executed. Maybe other parts of your Power Query code are being folded in by wrapping your original query in another CTE. Typically if you are going to hand code a SQL query you would want to do any renaming or filtering in the query and avoid doing too much extra manipulation after the query.

View solution in original post

6 REPLIES 6
v-janeyg-msft
Community Support
Community Support

Hi,  @Anonymous 

 

It seems to be a piece of sql code, of course it doesn't conform to pq syntax. I recommend you connect to the data source by using connector and you can enter sql code below.

Like this:

vjaneygmsft_0-1637638257484.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

Anonymous
Not applicable

The only difference I have from what you have is that mine is set to directquery rather than import, but that is how I added in the connection and still no luck.

d_gosbell
Super User
Super User

I'm pretty sure Power Query will only let you enter a single statement, not a multi-statement batch like you have. You can remove the "USE" statement since you already specify the database as part of the connection. Then if you remove the 2 "DECLARE" calls and just put the values of 7 and 9 directly into the query it should work fine. The other option would be to wrap the declares and the query into a stored procedure and then just call that.

Anonymous
Not applicable

I have done what you have suggested and now I am getting "Mcrosoft 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 or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ')'.    "

 

Wht might be the new issue? I can understand what the error is saying, but do run the query in SSMS and I am able to preview data in PowerBI. just when I apply it is when I get the error.

Sorry, but I cannot replicate this issue. I've created a query with a CTE and it works both in preview and when I click apply. The error looks like it is coming from SQL Server. My suggestion would be to run a SQL Profiler trace to capture the actual query that is being executed. Maybe other parts of your Power Query code are being folded in by wrapping your original query in another CTE. Typically if you are going to hand code a SQL query you would want to do any renaming or filtering in the query and avoid doing too much extra manipulation after the query.

Anonymous
Not applicable

I do apologize. I must have copied something wrong over from my SQL query into PowerBI. I removed everything and then re-added in the query. This time, there were no errors. I appreciate the assistance.

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors