Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sivarammoto
Frequent Visitor

Invalid Literal error while passing parameter in advanced query editor

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.

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Community Support
Community Support

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors