Passing date variable in Power Query to ODBC source

I have managed to extract a date from Excel (that the user can amend).[A named range called DateB]. I want to be able to pass that to a variable in the sql part of Power Query so that various checks can be made before passing the data back. However, I cannot get the variable @man_Date to accept the date that I have extracted.

RDP3 returns 30/06/2020 correctly at that stage.

However, in the source part, if I have ” & RDP3 & ” I am told it won’t work with the &s If I remove them I am then left with the error message “Token comma expected”.

This is the code I have at the start of the query. I also use the @Man_Date in a subquery as well.

What would be the correct syntax to use for RDP3? @Man_Date works with a manual entry (@Man-Date = ‘2020-06-30’) I have tried various date formats, but cannot seem to get this to work. It seems both parts work independently. It’s just tying them together that seems to be the sticking point.

let
    //
    RDP = Excel.CurrentWorkbook(){[Name="DateB"]}[Content],
    RDP2 = Table.TransformColumnTypes(RDP,{{"Column1", type date}}),
    RDP3 = RDP2{0}[Column1],
    //
Source = Odbc.Query("dsn=xxxxxxxxxxx", "

--PI Payments Due
SET NOCOUNT ON;
DECLARE @man_Date DATETIME --Manual Date
SET @man_Date = " & RDP3 & "

select qry1.*,
case when [Invoice Due Date] > @man_Date or datediff(d,[Invoice Due Date], @man_Date) = 0 then [Sterling Balance Due] else 0 end as 'Current',
case when datediff(d,[Invoice Due Date], @man_Date) >   0 and datediff(d,[Invoice Due Date], @man_Date) <=  30 then [Sterling Balance Due] else 0 end as '0-30',
case when datediff(d,[Invoice Due Date], @man_Date) >  30 and datediff(d,[Invoice Due Date], @man_Date) <=  60 then [Sterling Balance Due] else 0 end as '31-60',
case when datediff(d,[Invoice Due Date], @man_Date) >  60 and datediff(d,[Invoice Due Date], @man_Date) <=  90 then [Sterling Balance Due] else 0 end as '61-90',
case when datediff(d,[Invoice Due Date], @man_Date) >  90 and datediff(d,[Invoice Due Date], @man_Date) <= 120 then [Sterling Balance Due] else 0 end as '91-120',
case when datediff(d,[Invoice Due Date], @man_Date) > 120  then [Sterling Balance Due] else 0 end as 'Over 120'

from (

    select  VD.vcd_voucherno as 'Invoice No',VD.vcd_voucherdate as 'Date Raised',  PTY_NmeParty as 'Name' ,
    PTY_ValBuyerCreditLimit as 'Credit Limit',vd.vcd_currencycode as 'Currency',
    vd.vcd_amount * case when vd.vcd_debitorcredit = 'D' then -1 else 1 end as 'Invoice Amount', 
    (coalesce(sum(SOC.sof_crsetoffamount),0) - coalesce(sum(SOD.sof_drsetoffamount),0)) as 'Amount Paid',

Answer

I have now learnt that I can only pass text to the sql. So I have used the below breakdown to get the date into a text form for SQL.

let
//
RDC1 = Excel.CurrentWorkbook(){[Name="DateB"]}[Content],
RDC2 = Table.TransformColumnTypes(RDC1,{{"Column1", type date}}),
RDC3 = RDC2{0}[Column1],
RDC4 = Date.ToText(RDC3, "yyyy")&"-"&Date.ToText(RDC3, "MM")&"-"&Date.ToText(RDC3, "dd"),
// Source = Odbc.Query("dsn=xxxxxxxx", "#(lf)#(lf)--PI Payments Due#(lf)SET NOCOUNT ON;#(lf)DECLARE @man_Date DATETIME --Manual Date#(lf)SET @man_Date = **'" &Text.From(RDC4)& "'**  #(lf)Select etc

It is the RDC4 part that converts it to a useable text date and is used throughout the query.

Hope that helps someone else.

Advertisement

Answer