#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] Using Parameters in Querying Data from Snowflake with ODBC

## sergiu11

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.

----------


## AliGW

Welcome to the forum.  :Smilie: 

The instructions for attaching sample workbooks are at the top of the page.

----------


## sergiu11

Thank you Ali  :Smilie: , 

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.  :Frown:

----------


## CK76

When you are passing parameter/variable via PowerQuery connection. You'll need to edit to M-code using Advanced Editor (in query editor).

Declare a variable (which you can update from a cell in the workbook), and then use the variable to construct your query string.

To pass value from cell to the query... first set up named range for single cell (ex: vVal = Sheet1!$A$1).
Then add following after "let" line in M-code.



```
Please Login or Register  to view this content.
```


But if parameter you are passing is date or numeric, you must convert it to text and nest it in single quote "'".

Ex:



```
Please Login or Register  to view this content.
```


Then you can edit query to something like... (using MsSQL as example...)



```
Please Login or Register  to view this content.
```


Where #(lf) represents new line.

*EDIT:*
Similar concept can be applied to most Query connection using PowerQuery. Do note there are some disadvantages to this method (Ex: editing source by database query window will not be available and you must edit query directly in formula bar or in advanced editor.)

Alternately you can turn on Legacy MS Query using Options to use old method.

----------


## sergiu11

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

----------


## CK76

So, you have set up named range, "pmonat" for the month value.

You'd pass it on to PowerQuery using same method.



```
Please Login or Register  to view this content.
```


I'm assuming that you have the cell formatted as text and leading zero's are inputted by the user.

Now to use this in your query. You'll need to concatenate these variables into respective sections of the query...
It should look like something like below...



```
Please Login or Register  to view this content.
```


I assumed you wanted qplager used in the list for "where lager in" statement, and qpmonat as month number.

Note: You may receive Formula Firewall error. This is usually caused by security setting mismatch between worksheet and the database. If that happens, go in to data source settings and set both the workbook's and db's security level to same level (ex: set both to organizational).

----------


## sergiu11

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.

----------


## CK76

You are welcome  :Smilie: 

If you are satisfied with the solution provided. Please mark the thread as solved using Thread tools found at top of your initial post.

----------

