Search code examples
odbcpowerquery

Passing Power Query variable/parameter in an Odbc.Query() formula in Excel

I am a SQL statement dummy trying to load a simple import via ODBC connection in excel, and below is the query that works fine.

let

D_FROM1 = Excel.CurrentWorkbook(){[Name="DATE_FROM"]}[Content],
D_FROM2 = Table.TransformColumnTypes(D_FROM1,{{"Column1", type date}}),
D_FROM3 = D_FROM2{0}[Column1],
D_FROM4 = Date.ToText(D_FROM3, "yyyy") & "-" & Date.ToText(D_FROM3, "MM") & "-" &     Date.ToText(D_FROM3, "dd"),

Source = Odbc.Query(
                      "dsn=My Server", 
   "
SELECT 
POPOINLineItem.documentDate,
POPOINLineItem.postingDate,
POPOINLineItem.reference,

POStatusDefinition.name AS 'PO Status',

FROM 
POPOINLineItem POPOINLineItem,
POStatusDefinition POStatusDefinition,

WHERE 
POPOINLineItem.myStatus = POStatusDefinition.oid

AND
PO Status = 'Entered'
AND
POPOINLineItem.postingDate >= {d '2023-12-31'}
 "
                    )
in
Source

Challenge: I want to pass a dynamic date filter in the final AND statement, instead of hardcoding it with "{d '2023-12-31'}" (ODBC date format), as user doesn't know how to amend in power query but instead know how to update an excel cell & hit refresh query.

The "Let session" was my trial to set up a variable in same power query to be used in the Odbc.Query() statement, but how can I reference correctly and pass the filter, replacing {d 'yyyy-mm-dd'}? Or Excel workbook will never be able to be referenced in the stage of ODBC data export?

The option of not filtering date from export and then do it in excel is not considered as dataset would be too large, time difference is a few seconds vs 5 min+.

I thank you in advance for any guidance!

Solution

  • Try the following, you simply refer to the step name:

    ...
D_FROM4 = Date.ToText(D_FROM3, [Format="yyyy-MM-dd"]),

...
POPOINLineItem.postingDate >= {d '" & D_FROM4 & "'}
...