Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]
Solved! Go to 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.
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:
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
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.
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.
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
4 | |
4 | |
3 | |
2 | |
2 |