A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Token Comma expected error when calling powerquery function
I am trying to read in a parameter the user entered from an Excel sheet and pass that into my Azure query but am getting the Token Comma expected error.
Can someone please help me resolve? It runs if the number if hardcoded but the moment I enter the function and try and concatenate the string I get this error.
let
Source = Sql.Database("mapaersprosqlserver.database.windows.net", "mapaerspro01sqloutbound", [Query="DECLARE @Year INT = "& fParameter(""ParameterTable"",""MyYear"") & ";#(lf)#(lf)SELECT *#(lf)FROM (SELECT be.EventType,be.RaisedAt,be.OrganisationNumber,be.OrganisationName,be.Year,be.[Key],be.Description,be.Severity#(lf)#(tab)#(tab)FROM event.vw_BusinessException be#(lf)#(tab)#(tab)#(tab) CROSS APPLY event.fn_LatestEventIds(be.EventType) ids#(lf)#(tab)#(tab)#(tab) INNER JOIN (SELECT IntegrationEventId,RaisedAt,OrganisationNumber#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)FROM (SELECT DISTINCT IntegrationEventId,RaisedAt,OrganisationNumber,ROW_NUMBER() OVER (PARTITION BY OrganisationNumber ORDER BY RaisedAt DESC) rn#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) FROM outbound.vw_StaffingEntitlement_Component) q#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)WHERE rn = 1) q#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)ON be.IntegrationEventId = q.IntegrationEventId#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)WHERE be.Year = @Year#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)AND ids.IntegrationEventId = be.IntegrationEventId#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)AND ids.Year = be.Year#(lf)#(tab) UNION#(lf)#(tab) SELECT be.EventType,be.RaisedAt,be.OrganisationNumber,be.OrganisationName,be.Year,be.[Key],be.Description,be.Severity#(lf)#(tab)#(tab)FROM event.vw_BusinessException be#(lf)#(tab)#(tab)#(tab) CROSS APPLY event.fn_LatestEventIds(be.EventType) ids#(lf)#(tab)#(tab)#(tab)INNER JOIN (SELECT IntegrationEventId,RaisedAt,OrganisationNumber#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)FROM (SELECT DISTINCT IntegrationEventId,RaisedAt,OrganisationNumber,ROW_NUMBER() OVER (PARTITION BY OrganisationNumber ORDER BY RaisedAt DESC) rn#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) FROM outbound.vw_OperationalFunding_Component) q#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) WHERE rn = 1) q#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) ON be.IntegrationEventId = q.IntegrationEventId#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) WHERE be.Year = @Year#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) AND ids.IntegrationEventId = be.IntegrationEventId#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab) AND ids.Year = be.Year) XX#(lf)ORDER BY RaisedAt DESC,OrganisationNumber ASC,CASE Severity WHEN 'Fix' THEN '1' WHEN 'Action' THEN '2' WHEN 'Info' THEN '3' ELSE 4#(lf)#(lf)END;"])
in
Source
Microsoft 365 and Office | Excel | For business | Windows
Community Center | Not monitored
Tag not monitored by Microsoft.