Invalid Literal error while passing parameter in advanced query editor

[sivarammoto]

Hi

I am new to Power BI, i searched in power BI forum and tried multiple times but still getting error so posting here.

I am executing simple select statement using parameter but getting invalid literal error or synatax error in the advanced editior.

Steps to replicate issue :

Source : Azure SQL server

Created parameter called empid as text type

calling this parameter in advanced editor like below

let
Source = Sql.Database("advt.database.windows.net", "advt", [Query="select * from stg.emp #(lf)where EMPID = "&empid&"])
in
Source

I think there is some issue at empid syntax. If i remove parameter and enter hardcode value then its working fine.

[Anonymous]

Getting invalid literal soon after pasting query in advanced editor- Not sure where exactly is the error. seems to be running fine in Azure data explorer.

let KustoQuery =
let Source = Json.Document(Web.Contents("https://icmclusterlb.kustomfa.windows.net/v1/rest/query?db=IcmDataWarehouse&csl=.show version",[Query=[#"csl"="cluster('icmclusterlb.kustomfa.windows.net').database('IcmDataWarehouse').Incidents#(cr,)#(lf)| where CreateDate >= datetime(""2019-01-01"")#(cr,)#(lf)| where OwningTenantId == ""22015""#(cr,)#(lf)| where OwningTeamId in (""34509"", ""61160"")#(cr,)#(lf)| where Status in (""MITIGATED"",""RESOLVED"",""ACTIVE"")#(cr,)#(lf)| where Severity <= 4 #(cr,)#(lf)| summarize ModifiedDate = arg_max(ModifiedDate,*) by IncidentId#(cr,)#(lf)| project CreateDate, ModifiedDate, IncidentId, Severity,OwningTeamName, OwningContactAlias, Status,Title, SourceCreatedBy, OccurringEnvironment, ParentIncidentId, ImpactStartDate, IncidentType#(cr,)#(lf)| sort by CreateDate asc",#"x-ms-app"="PowerQuery",#"properties"="{""Options"":{""servertimeout"":""00:04:00""},""Parameters"":{},""PrincipalIdentity"":null,""ClientRequestId"":null,""SecurityToken"":null,""AuthorizationScheme"":null,""RequestHostName"":null,""LocalClusterName"":null,""Application"":null,""User"":null}"], Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "DataType", "Type" },
{
{ "Double", Double.Type },
{ "Int64", Int64.Type },
{ "Int32", Int32.Type },
{ "Int16", Int16.Type },
{ "UInt64", Number.Type },
{ "UInt32", Number.Type },
{ "UInt16", Number.Type },
{ "Byte", Byte.Type },
{ "Single", Single.Type },
{ "Decimal", Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String", Text.Type },
{ "Boolean", Logical.Type },
{ "SByte", Logical.Type },
{ "Guid", Text.Type }
}),
Exception = Source[Exceptions]?{0}?,
Result = if (Exception <> null) then
error Exception
else
let
DataTable = Source[Tables]{0},
Columns = Table.FromRecords(DataTable[Columns]),
ColumnsWithType = Table.Join(Columns, {"DataType"}, TypeMap , {"DataType"}),
TableRows = Table.FromRows(DataTable[Rows], Columns[ColumnName]),
TypedTable = Table.TransformColumnTypes(TableRows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3} }))
in
TypedTable
in
Result
in KustoQuery

[v-frfei-msft · Accepted solution]

Hi @sivarammoto ,

To update your M code in Power query as below.

Spoiler

let
Source = Sql.Database("advt.database.windows.net", "advt", [Query="select * from stg.emp #(lf)where EMPID = #"empid"])
in
Source

Regards,

Frank