topic Dynamic M Query against Azure SQL Database - Direct Query in Developer

Dynamic M Query against Azure SQL Database - Direct Query

ALMalecha — Wed, 08 Jun 2022 13:00:50 GMT

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]

Re: Dynamic M Query against Azure SQL Database - Direct Query

ALMalecha — Wed, 08 Jun 2022 13:14:46 GMT

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

Re: Dynamic M Query against Azure SQL Database - Direct Query

Anonymous — Mon, 13 Jun 2022 06:37:20 GMT

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.