Power Query
Dynamic SQL queries with Excel’s Power Query

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:


You need to select from the DB based on a long list of specific records.

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)
  1. 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.

  1. 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.

  1. Add a new Custom column
  1. 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:

  1. 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.

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

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

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

  1. 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.

2 thoughts on “Dynamic SQL queries with Excel’s Power Query

    • Author gravatar

      Hello, stumbled on this post while having to create this for odbc subqueries similar to what you’re doing there. Thought this might help.
      [quote]
      let
      Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
      #”Changed Type” = Table.TransformColumnTypes(Source,{{“YEAR”, Int64.Type}, {“ITEM”, type text}}),
      #”Filtered Rows1″ = Table.SelectRows(#”Changed Type”, each ([YEAR] null and [ITEM] null)),
      aclean = (a as text) =>
      let
      // replace text replace with text contains and return null (this sadly is the closest thing to a regex in power query without vba modules)
      // a1 = Text.Replace(a,”‘”,””),
      // a2 = Text.Replace(a1,””””,””),
      // ares = Text.Replace(a2,”\”,””)
      a1 = Text.Trim(a),
      ares = if (Text.Contains(a1,”‘”) or Text.Contains(a1,”\”) or Text.Contains(a1,””””)) then null else a1
      in
      ares,
      afunc = (a as table) =>
      let
      a1 = Table.SelectColumns(a,”NITEM”), // manually specifying the field
      a2 = Table.Distinct(a1),
      a3 = Table.ToList(a2),
      ares = Text.Combine(a3,”,”)
      in
      ares,
      #”Clean” = Table.TransformColumns(#”Filtered Rows1″,{“ITEM”, each aclean(_)}),
      #”Filtered Rows” = Table.SelectRows(#”Clean”, each [ITEM] null and [ITEM] “”),
      #”Removed Errors” = Table.RemoveRowsWithErrors(#”Filtered Rows”),
      #”Added Custom1″ = Table.AddColumn(#”Removed Errors”, “NITEM”, each “‘”& [ITEM] & “‘”),
      #”Grouped Rows” = Table.Group(#”Added Custom1″, {“YEAR”}, {{“result”, each _, type table [NITEM=text]}}),

      T = Table.TransformColumns(#”Grouped Rows”,{“result”, each afunc(_)}),
      #”Added Custom” = Table.AddColumn(T, “Custom”, each “(ORDERYEAR = “&Number.ToText([YEAR])&” AND ICITEM IN(“&[result]&”))”),
      #”Removed Other Columns” = Table.SelectColumns(#”Added Custom”,{“Custom”}),
      #”Combine” = Table.ToList(#”Removed Other Columns”,Combiner.CombineTextByDelimiter(“”)),
      result = Text.Combine(#”Combine”,” OR “)
      in
      result
      [/quote]
      This will sanitize the data (use case will vary) and return an IN expression in the format [quote](ORDERYEAR = 2002 AND ITEM IN(’43’,’335′,’244′,’242′)) OR (ORDERYEAR = 2003 AND ITEM IN(‘3636′,’26346′,’64646′,’12536’))[/quote] an in expression here will optimize your query.
      To make it work with what you’re doing:
      a1 = table.addcolumn(mytable,”orderyear”, each Date.Year([orderdate]),
      a2 = table.distinct(a1)
      === there’s your years ^ and run it through query above to get the ins.
      To be clear, your query works fine, just the larger the dataset, the more likely sql will start to use more memory than it really needs to. Best

Leave a Reply

Your email address will not be published. Required fields are marked *