Using Parameters in Querying Data from Snowflake with ODBC

Hi everyone,

I had to change the data source of several queries in an Excel report. At the moment I am connecting to Snowflake via ODBC in Excel. Querying data from there works just fine, but the query from the old report had "?" as parameters. That was working in the old case because it was a Microsoft Query connection to the old database which is now obsolete. My issue is that now I cannot replicate adding such parameters inside the Snowflake query straight through ODBC.

The desired result is to have a separate tab where users can input their desired value of the parameter, and then the Snowflake query would pick that up and fetch only the filtered data. If we leave out such parameters, the file would very likely break just because of the large amount of unfiltered data.
Here is an example:

I would like to query this data into the "Data" tab:
select product, warehouse
from snowflake_table
where warehouse in ('?', 'Y', 'Z')
and year(date) = '2019' and month(date) = '12' and day(date) = '01'

And in a separate tab "Parameter", we have users typing in any other value next to Warehouse to query for the warehouse Y, Z as constants and the one typed in by them (let's say X), replacing the "?" (I did try without quotation marks as well). Ultimately I want to extend this to the year month day filters as well, but let's start with the warehouse for now.

When using a Microsoft Query connection, I would be prompted with a screen to connect the "?" with an input cell, but that is not working with the ODBC connection I set up to Snowflake.

Another issue is that with Snowflake we prompt users to sign in via an active directory for security reasons. Before the file had an embedded password and query to the old database via Microsoft Query.

Do you have any hints and tips how I could make this work?

Thank you in advance.

Thank you Ali ,

Unfortunately I am not sure how relevant it would be to attach the workbook. To check the query you would need credentials to our Snowflake via the active directory I mentioned. It is in company's policy not to share such information and unfortunately I cannot think of a different way to replicate the context of my issue without sharing credentials or private information. That is why I tried as much as I could to describe the situation in a more depersonalized way, even though I know it is worse for you who are trying to help me, sorry for that.

Hi CK76,

Thank you for your reply. It was definitely helpful as it set me into the right direction, but I still could not manage to make it work. Considering my non-existing skills with Excel M code and the Power Editor, I feel like most likely I messed up something.

Here is an overview with pics of what I have done on the topic. Please bear with the German set up of my Excel as I work here. I will mask the actual data and table names due to my company's policy:

1. So I have the 2 tabs: Konfiguration (where users can type in their parameters for the query) and Tabelle1 (where I want the data to be extracted). For the purpose of this test, I have 2 parameters in mind, one for the Lager (warehouse) where the input values can be "X", "Y", "Z" for example, and one for the Monat (month) in which case I imagine actually users would have to input "08", "09", "10", "11" or edited before hand to have accepted values for the query.

2. I have named the range as you have mentioned

1.PNG

3. Here is the overview of how I edited the Power Query as well. The named range is "plager", while "qplager" (qp stands for "query parameter") is defined after let

2.PNG

4. This is the query I used:

Please Login or Register  to view this content.

This is where I started getting confused. I tried several ways, but I failed in making it work. How exactly should I change the query to make it work? I imagine the one above goes to snowflake and it filters literally for 'qplanger', which is obliviously not what I want.
Ultimately I want to filter for year month day as well, so I am curious about the format here for months. If the users input in the "Konfiguration" Excel cell 6 for June, I would still need to send a "06" to the query, right?

The M code looks like this

Please Login or Register  to view this content.

Thanks again for the assistance.

Best regards,
Sergiu

It works perfectly now.

I got lost a bit in the where lager in ('" & qplager & "') part. But now that you have clarified it for me, I do not get any more errors.

Thank you, mister CK76 :D

You were very helpful and clear.