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

Question

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!

JSmart523 · Accepted Answer · 2024-01-18 04:31:19Z

1

For stuff like single-value data, I've learned to group all of them into a table in Excel with column names like "Name", "Value", and "Description".

Depending on who's editing the workbook and how much freedom I'm willing and able to allow for, it might be a hidden tab and the values are grabbed from other places on the workbook, or I might style the "Value" column as Input (because I like styles more than editing specific cells, but you can format the cell and disable protection that way instead) and then lock the tab so users can only edit the "Values" column and not add fields or change the "Name" column.

If you import this config table into a query called "ConfigTable" then you can have a query called "PostDate" with code of

Date.From(
  ConfigTable{[Name = "PostDate"]}[Value]
)

Or, you could go one step further and use Record.FromTable, making a query called "ConfigRecord" like

Record.FromTable(ConfigTable)

and then the "PostDate" query could just be

Date.From(
  ConfigRecord[PostDate]
)

Then, when creating your SQL string, you would replace

"...{d '2023-12-31'}..."

with

"...{d '" & Date.ToText(PostDate, "yyyy-MM-dd") & "}..."

Obviously you don't need all those to be separate queries, but I find that making them separate makes me think about how they can each be re-used, and the smaller each query is the harder it is for bugs to sneak in. For example, what if PostedDate is null? By isolating the value in its own query, you can add logic and guard clauses into these tiny queries without making rest of what you're trying to do unreadable.

edited Jan 18, 2024 at 4:31

answered Jan 18, 2024 at 4:17

JSmart523

2,5351 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DuckyShiny Over a year ago

Thanks for your advice, I will revisit when I get to do some bigger reports.

Sam Nseir · Accepted Answer · 2024-01-18 03:42:53Z

0

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 & "'}
...

answered Jan 18, 2024 at 3:42

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related