Dynamic M Query against Azure SQL Database - Direct Query

[ALMalecha]

I'm creating a PBI report that utilizes Direct Query, and we want it to return one record.

I've been using the article Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Docs for reference, and have my query updated. 

WorkOrderID is a parameter created in Manage Parameters. 

let   
    selectedworkorder = Text.Combine ({"'", Number.ToText(WorkOrderID), "'"}),
    dynamicfilter = Text.Combine({" where dm.workorderid = ",  selectedworkorder}),
    
    reportquery = "
           Select DM.Division, DM.Foreman, DM.Customer, J.PONumber, DM.JobName, DM.Yard, DM.WorkDate, weekend.Date AS WeekEndDate, DM.WorkStart, DM.LunchStart, DM.LunchEnd, DM.WorkEnd, DM.InvoiceProperty1Label, DM.InvoiceProperty1Value, DM.InvoiceProperty2Label,  DM.InvoiceProperty2Value, DM.InvoiceProperty3Label, DM.InvoiceProperty3Value, DM.InvoiceProperty4Label, DM.InvoiceProperty4Value, DM.InvoiceProperty5Label, DM.InvoiceProperty5Value, DM.BillableType, DM.WorkActivity, DM.MasterTimesheetID,           DM.WorkOrderID          from dbo.WorkOrderDataMart DM         join dbo.DimJob J on DM.JobID = J.JobID          join dbo.FactTimesheet t on DM.WorkOrderID = T.TimesheetID         join dbo.DimDate weekend on t.WeekEndDateID = weekend.DateKey ",

    FinalQuery = Text.Combine({reportquery, dynamicfilter}),

    Source = Sql.Database("proeazuretest.database.windows.net", "WJC", FinalQuery)  
    
in
    Source

However, I'm getting the below error.   When I pull out the query itself and run against our DB, it works fine.   I'm wondering if I have a syntax error, or if someone has encountered this, and found a resolution?

Thank you, 

Expression.Error: We cannot convert the value "
Select ..." to type Record.
Details:
Value=
Select DM.Division, DM.Foreman, DM.Customer, J.PONumber, DM.JobName, DM.Yard, DM.WorkDate, weekend.Date AS WeekEndDate, DM.WorkStart, DM.LunchStart, DM.LunchEnd, DM.WorkEnd, DM.InvoiceProperty1Label, DM.InvoiceProperty1Value, DM.InvoiceProperty2Label, DM.InvoiceProperty2Value, DM.InvoiceProperty3Label, DM.InvoiceProperty3Value, DM.InvoiceProperty4Label, DM.InvoiceProperty4Value, DM.InvoiceProperty5Label, DM.InvoiceProperty5Value, DM.BillableType, DM.WorkActivity, DM.MasterTimesheetID, DM.WorkOrderID from dbo.WorkOrderDataMart DM join dbo.DimJob J on DM.JobID = J.JobID join dbo.FactTimesheet t on DM.WorkOrderID = T.TimesheetID join dbo.DimDate weekend on t.WeekEndDateID = weekend.DateKey where dm.workorderid = '32253'
Type=[Type]

[ALMalecha · Accepted solution]

Figured it out - it was a syntax error:

The source statement should have been:  Source = Sql.Database("proeazuretest.database.windows.net", "WJC", [Query=FinalQuery]) 

let   
    selectedworkorder = Text.Combine ({"'", Number.ToText(WorkOrderID), "'"}),
    dynamicfilter = Text.Combine({" where dm.workorderid = ",  selectedworkorder}),
    
    reportquery = " Select DM.Division, DM.Foreman, DM.Customer, J.PONumber, DM.JobName, DM.Yard, DM.WorkDate, weekend.Date AS WeekEndDate, DM.WorkStart, DM.LunchStart, DM.LunchEnd, DM.WorkEnd, DM.InvoiceProperty1Label, DM.InvoiceProperty1Value, DM.InvoiceProperty2Label,  DM.InvoiceProperty2Value, DM.InvoiceProperty3Label, DM.InvoiceProperty3Value, DM.InvoiceProperty4Label, DM.InvoiceProperty4Value, DM.InvoiceProperty5Label, DM.InvoiceProperty5Value, DM.BillableType, DM.WorkActivity, DM.MasterTimesheetID, DM.WorkOrderID from dbo.WorkOrderDataMart DM  join dbo.DimJob J on DM.JobID = J.JobID  join dbo.FactTimesheet t on DM.WorkOrderID = T.TimesheetID join dbo.DimDate weekend on t.WeekEndDateID = weekend.DateKey",

    FinalQuery = Text.Combine({reportquery, dynamicfilter}),

    Source = Sql.Database("proeazuretest.database.windows.net", "WJC", [Query=FinalQuery])  
    
in
    Source

[v-rzhou-msft]

Hi @ALMalecha ,

I am glad that you can figure out your problem. Please kindly accept your workaround as the solution. More people who with similar problems will benefit from it.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.