Excel cell Value as SQL query where statement

user_18160292 0
Viewed 932

I am very new to SQL, I want to import data from SQL Server to Excel using this query

SELECT 
    Model, Factory, TargetTime, TotalEvalMins 
FROM 
    AMSView 
WHERE 
    WeekNumber = 45 AND WeekYear = 2021

I want to change the week number & year dynamically by taking user input from a cell.

Can anyone please suggest how to change the query?

Let's say the user values week & year in worksheet sample in A1,A2 , how can I write that query?

Since the amount of data is huge I must apply where while querying the data instead of applying filters in Excel.

Sorry for my bad English

parameters excel sql database
1 Answers

Name each of your cells that you will use as parameters. This page describes the process.

Name a cell

1. Select a cell.

2. In the Name Box, type a name.

3. Press Enter.

For each cell containing a parameter for your query:

  1. Select the cell
  2. Use Data>Get & Transform Data>From Table/Range. This will open the PowerQuery Editor. You will see something like this:

enter image description here

  1. Right-click the cell in row 1 in the grid in the Power Query Editor and select 'Drill Down'. This converts the query on your parameter cell to a named value which can be used in other queries. It looks like this:

enter image description here

Now in Excel, use Data>Get Data and create your query from the database. I created a sample table in a local SQL Server database called AMSView, then connected to it with the query text in your post. When finishing the query connection, select 'Transform' so the query opens in the PowerQuery Editor.

Now, use Home>Advanced Editor and edit as follows by replacing the fixed values in the WHERE clause with concatenated names of your parameter cells, converted to text. For brevity, I have only used one parameter. If you've used capital letters in your cell names, remember, the M language is case-sensitive, so the concatenated parameter name must have identical casing to the named value.

let
    Source = Sql.Database("localhost", "StackOverflowTest", [Query="SELECT #(lf)    Model, Factory, TargetTime, TotalEvalMins #(lf)FROM #(lf)    AMSView #(lf)WHERE #(lf)    WeekNumber = " & Number.ToText(week_number)])
in
    Source

Once your query is finished, use Home>Close & Load to load the results to the workbook. Now, when your parameter cells change, you need only refresh the query (right-click, refresh) and the data will be filtered as required.

user_15719500
Related