Power Query - Parameters type Date on SQL Source |Power Query|Excel Forum|My Online Training Hub

Hi,

I'm trying to put a paremeters from date type on SQL Source but I have the following error:

DataSource.Error: Microsoft SQL: Incorrect syntax near 'lf'.
Details:
DataSourceKind=SQL
DataSourcePath=datacenter1.compulab.pt\server72,1472;Alfabares_WMS
Message=Incorrect syntax near 'lf'.
ErrorCode=-2146232060
Number=102
Class=15

_______________________________________________________

The code is:

let

ParamTable1 = Excel.CurrentWorkbook(){[Name="Rececao"]}[Content],
Param1 = "'" & Text.From(ParamTable1[Data]{0}) & "'",
Source = Sql.Database("datacenter1.compulab.pt\server72,1472", "Alfabares_WMS", [Query="#(lf)select sku, max (Validade) as ValidadeMax from (#(lf)select ent.docstatus as statusPHC,format(enc.DocDate, 'dd-MM-yyyy') as DataRececao,enc.ERPDocNum, e.Name as Fornecedor, linhaenc.SKU,sk.Name as SKUName,linhaenc.Qty as QTDENcomendada,loteentrada.Qty as QTDConfirmada,#(lf)loteentrada.NetWeight as PesoConfirmado,lote.Batch, DATEDIFF (day, enc.DocDate, lote.ValidityDate) as diasValidade, convert(varchar,lote.ValidityDate,23) as Validade#(lf), linhaentrada.changedate#(lf), lote.ChangeBy as Responsavel#(lf)from [Alfabares_WMS].[dbo].T_WMS_Doc enc#(lf) join [Alfabares_WMS].[dbo].T_WMS_DocDetail linhaenc on enc.DocID=linhaenc.DocID#(lf) left join [Alfabares_WMS].[dbo].T_WMS_DocDetail linhaentrada on linhaenc.DocDetailID=linhaentrada.RelatedLineID#(lf) left join [Alfabares_WMS].[dbo].T_WMS_DocDetailBatch loteentrada on linhaentrada.DocDetailID=loteentrada.DocDetailID#(lf) left join [Alfabares_WMS].[dbo].T_WMS_Batch lote on loteentrada.BatchID=lote.BatchID#(lf) join [Alfabares_WMS].[dbo].T_WMS_DocDefinition encdef on enc.DocDefID=encdef.DocDefID#(lf) join [Alfabares_WMS].[dbo].T_WMS_SKU sk on linhaenc.SKU=sk.SKU#(lf) join [Alfabares_WMS].[dbo].T_WMS_Entity e on enc.EntityID=e.EntityID#(lf) left join [Alfabares_WMS].[dbo].T_WMS_Doc ent on linhaentrada.DocId=ent.DocId#(lf)where enc.DocDefID in (24) and enc.ERPDocSeries like (2) and format(enc.DocDate, 'yyyy') >=2020 and
convert(varchar,enc.DocDate,23)< convert(varchar,"&Param1 & ",23) lf) ) as A#(lf)where QTDConfirmada>0#(lf)group by SKU"])
in
Source

___________________________________

The Param1 - has filled with 06/01/2021

Any one can help me.

Thanks.

Cristina