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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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