Dynamic SQL queries with Excel’s Power Query

Use an excel table to modify your SQL query

If you regularly run queries to any database in your workplace, chances are you have encountered a user request like this:

We can use Excel to query the database and get the information requested, but you will have to manually write the WHERE conditions that include the Sales Order number and the year. But what if you receive these requests 2-3 times a week from several different users? Wouldn’t it be nice if you (or your end user) could just copy and paste the list of desired values into a table, and after refreshing the file see in the spreadsheet the new results within seconds?

We’ll achieve this by writing a dynamic SQL statement that will build the WHERE conditions based on the list of values that we will have in the Requested Sales Orders table.

Build dynamic WHERE conditions

1. Connect to your database Using the “Get and Transform Data” options. Just go to Data -> Get Data -> Database – > MySQL (or SQL, Oracle, IBM depending on your database)

2. In the pop up, fill out with Server, Database and open the advanced options so you can paste/write your SQL code.

Write the SQL with all the columns that you will need in final the result and optionally, write “limit 10” at the end to avoid loading too many records (if SQL Server, you will have to use the TOP 10 statement). Click OK and then load your results to a spreadsheet

This selected the columns we wanted in the report, now we need to let Excel know which specific rows we want.

3. Copy the list of values with the year and paste into a new spreadsheet. Format it as a table, we are going to name it as “Sales_Orders”

This will take you to the Query Editor. Let’s change the Year column data type to Text

Now, before moving on to the next step, let’s review quickly how your SQL would look if you were to write the conditions by hand for two sales orders:

Basically, we need to be able to generate (salesordernumber = '[SALES ORDER NUMBER]' and year(orderdate) = [SALES ORDER YEAR]) by each row we have in the Sales Order table.

5. Add a new Custom column

6. In the formula box, write the condition(s) that should be in the final SQL statement. In this case, we need Sales Order and Year of the Order date to match what we have in each of the columns of our list of values

This should be the result when you add the columns (make sure you concatenate correctly by using the quotation marks (“) and the & sign):

Note that Sales Order Number is a string value so is wrapped in single quotation marks (just like it would be wrapped in a regular SQL statement). Year is integer so we don’t have quotation marks around.

Now hit OK and this should be the result:

7. Convert the new column to a list:

This will be the result:

We are almost there and now we need to put all those rows together. The final set of conditions should look like this: ([SALES ORDER #] = 'XYZ' AND YEAR = 2000) OR ([SALES ORDER #] = 'ABC' AND YEAR = 2001) OR ([SALES ORDER #] = 'OPQ' AND YEAR = 2003)…

To achieve this, we’re going to use the “Text.Combine” function.

8. Go to the Formula bar and write Text.Combine( right before the “#” sign, and at the very end write a comma and then ," OR ")

Before:

After:

9. We just need to add “WHERE” at the beginning of the text. Write " WHERE "& in the formula bar

After that, we will have all the conditions together in one single text, with the WHERE keyword:

10. Let’s go back to the main query and edit the SQL statement, and click on the “Source step”

1. Remove the “limit 10” part and Let’s concatenate the existing query with the name of the query that has all the conditions we just defined

12. Depending on your power query set up, you can get the following error, this is fixed in the Query Options screen

Go to file -> Options and Settings -> Query Options

Go to Privacy section and make sure the second option is selected

After that, error will go away:

13. Close and load your changes and you will see the table in the spreadsheet with the new results:

All set! Next time you get a similar request:

Just copy and paste the values into the “Sales_Orders” table and hit “refresh all”:

And see new results showing up in SECONDS! If a user has SELECT permissions to the database this is a good alternative for them to modify the statement without having to deal with writing code.