SIGN IN
LOG IN

Dynamic SQL queries with Excel’s Power Query

80 Comments / Power Query / By

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.

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

  1. Matt

    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

  2. Matt

    cant edit my comment for some reason. just noticed you’re converting the year value in the query itself. so yea (year(orderdate) = ….) Best

  3. jacquettapeebles

    When I originally left a comment I appear to have clicked the -Notify me when new comments are added- checkbox and from now on every time a comment is added I receive four emails with the same comment. Perhaps there is a means you are able to remove me from that service? Thanks!

  9. Vocal.Media

    Hey I am so thrilled I foumd your site, I really found you by error,
    while I was researching on Digg for something else, Regardless I am here now and would ust like to say thank you for a incredible post and a all round enjoyable blog (I
    akso love the theme/design), I don’t have time to read itt all att the moment but I
    have book-marked it and also added your RSS feeds,
    so when I have time I will be back to read much more, Please do
    keep up the excelllent job. https://vocal.media/authors/aviatorgame-151l0w8n

  39. Band.Us

    I hardly create comments, but i diid some searching and wouhd up here Dynamic SQL queries with Excel’s Poqer Query – POWER GI.

    Andd I actually do have a couple of questions forr yoou if it’s allright.

    Is it just me or does it seem like some of these responses look like
    left by brain dead individuals? 😛 And, if you are posting at other online social sites, I’d like to follow everything
    new you have to post. Would you list of the complete urls off your
    ppublic sites like ylur Facebook page, twitter feed, or linkedin profile? https://band.us/band/95064077/post/1

  54. https://sguru.org/how-the-online-show-crazy-time-brings-together-players-from-all-over-the-world

    Hey would you mind stating which blog platform you’re working with?
    I’m planning to start mmy own blog in the near future but
    I’m having a tough time choosing beween BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design seems different then most blogs aand I’m looking for something completely unique.

    P.S Sorry forr getting off-topic but I had to ask! https://sguru.org/how-the-online-show-crazy-time-brings-together-players-from-all-over-the-world

  56. https://www.storeboard.com/blogs/gaming/tracing-the-evolution-of-crazy-time-tv-show-bonus-rounds/5771972

    I’m reallly inspired together with your writing talents as well as ith
    the structure for your blog. Is that this a paid
    subject or did you customize it yourself?
    Either way keep uup the excellet quality writing, it is rzre to see
    a great weblog like this one today.. https://www.storeboard.com/blogs/gaming/tracing-the-evolution-of-crazy-time-tv-show-bonus-rounds/5771972

  61. Blog.Rackons.in

    Hey I am sso happy I found your webpage, I
    really found youu by mistake, while I wwas looking
    on Bing for something else, Nonetheless I aam here now and would just like to say
    thanks a lot for a marvelous post and a all round exciting blog (I also
    love the theme/design), I don’t have time to look over it all
    at the mment but I have book-marked it and also added in your RSS feeds,
    so when I have time I will be back to read a great deal more,Please
    do keep up the excellent work. https://blog.rackons.in/player-reviews-of-crazy-time-what-users-are-saying

  80. jimhentze780

    Definitely believe that which you stated. Your favorite reason seemed to be on the internet the simplest thing to be aware of. I say to you, I certainly get irked while people consider worries that they just don’t know about. You managed to hit the nail upon the top as well as defined out the whole thing without having side-effects , people could take a signal. Will probably be back to get more. Thanks

Leave a Comment

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

Alzheimers Digest is a monthly grassroots newspaper published inspired by gloria grimes who is inflicted with Alzheimers Dementia.

Useful Links

Alzheimers Digest

  • April 2019
  • July 2019
  • Nov 2019

Get in Touch

Instagram Twitter Facebook
Subscribe to our newsletter
Linkedin Youtube Envelope

© POWER[GI] 2024

Subscribe to our newsletter
Linkedin Youtube Envelope
HOMEABOUT USSERVICES
BLOGCONTACT
© POWER[GI] 2021