Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    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

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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