{"id":2326,"date":"2020-05-17T21:55:42","date_gmt":"2020-05-17T21:55:42","guid":{"rendered":"https:\/\/portfolio.accrualhub.com\/powergi2\/?p=205"},"modified":"2025-03-05T20:51:17","modified_gmt":"2025-03-05T20:51:17","slug":"dynamic-sql-queries-with-excels-power-query","status":"publish","type":"post","link":"https:\/\/powergi.net\/de\/blog\/dynamic-sql-queries-with-excels-power-query\/","title":{"rendered":"Dynamic SQL queries with Excel\u2019s Power Query"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n

Use an excel table to modify your SQL query<\/h2>\n\n

If you regularly run queries to any database in your workplace, chances are you have encountered a user request like this:<\/p>\n\n

\"\"\n

You need to select from the DB based on a long list of specific records.<\/figcaption>\n<\/figure>\n\n

We can use Excel to query the database and get the information requested, but you will have to manually write the WHERE<\/em> 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\u2019t 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?
This is where Power Platform consulting<\/strong><\/a> can greatly enhance efficiency by automating parts of the workflow and ensuring seamless data retrieval.<\/p>\n\n

We\u2019ll achieve this by writing a dynamic SQL statement that will build the WHERE<\/em> conditions based on the list of values<\/strong> that we will have in the Requested Sales Orders table.<\/p>\n\n

Build dynamic WHERE conditions<\/strong><\/h3>\n\n
    \n
  1. Connect to your database Using the \u201cGet and Transform Data\u201d options. Just go to Data -> Get Data -> Database \u2013 > MySQL (or SQL, Oracle, IBM depending on your database)<\/strong><\/li>\n<\/ol>\n\n
    \"\"<\/figure>\n\n
      \n
    1. In the pop up, fill out with Server, Database and open the advanced options so you can paste\/write your SQL code. <\/strong><\/li>\n<\/ol>\n\n

      Write the SQL with all the columns that you will need in final the result and optionally, write \u201climit 10\u201d 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<\/p>

      \t\t
      \n\t\t\t
      \n\t\t
      \n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t

      Turn your ideas into digital solutions<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

      \n\t\t\t\t
      \n\t\t\t\t\t

      Our team guides you step by step to build custom apps in Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

      \n\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t
      \n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t
      \n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n\n
      \"\"<\/figure>\n\n

      This selected the columns we wanted in the report, now we need to let Excel know which specific rows we want.<\/p>\n\n

      \"\"<\/figure>\n\n
        \n
      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 \u201cSales_Orders\u201d<\/strong><\/li>\n<\/ol>\n\n
        \"\"<\/figure>\n\n

        This will take you to the Query Editor. Let\u2019s change the Year column data type to Text<\/p>\n\n

        \"\"<\/figure>\n\n

        Now, before moving on to the next step, let\u2019s review quickly how your SQL would look if you were to write the conditions by hand for two sales orders:<\/p>\n\n

        \"\"<\/figure>\n\n

        Basically, we need to be able to generate (salesordernumber = '[SALES ORDER NUMBER<\/strong>]' and year(orderdate) = [SALES ORDER YEAR<\/strong>])<\/em> <\/code>by each row we have in the Sales Order table.<\/p>\n\n

          \n
        1. Add a new Custom column<\/strong><\/li>\n<\/ol>\n\n
          \"\"<\/figure>\n\n
            \n
          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<\/strong><\/li>\n<\/ol>\n\n
            \"\"<\/figure>\n\n

            This should be the result when you add the columns (make sure you concatenate correctly by using the quotation marks<\/strong> (\u201c<\/strong>) and the &<\/strong> sign):<\/p>\n\n

            \"\"<\/figure>\n\n

            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\u2019t have quotation marks around.<\/p>\n\n

            Now hit OK and this should be the result:<\/p>\n\n

            \"\"<\/figure>\n\n
              \n
            1. Convert the new column to a list:<\/strong><\/li>\n<\/ol>\n\n
              \"\"<\/figure>\n\n

              This will be the result:<\/p>\n\n

              \"\"<\/figure>\n\n

              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 <\/strong>([SALES ORDER #] = 'ABC' AND YEAR = 2001) OR<\/strong> ([SALES ORDER #] = 'OPQ' AND YEAR = 2003<\/code>)<\/em>\u2026<\/p>\n\n

              To achieve this, we\u2019re going to use the \u201cText.Combine\u201d function.<\/p>\n\n

                \n
              1. Go to the Formula bar and write Text.Combine(<\/code> <\/em>right before the \u201c#\u201d sign, and at the very end write a comma and then ,\" OR \")<\/code><\/em><\/strong><\/li>\n<\/ol>\n\n

                Before:<\/strong><\/p>\n\n

                \"\"<\/figure>\n\n

                After:<\/strong><\/p>\n\n

                \"\"<\/figure>\n\n
                  \n
                1. We just need to add \u201cWHERE\u201d at the beginning of the text. Write \" WHERE \"&<\/code><\/strong> in the formula bar<\/li>\n<\/ol>\n\n
                  \"\"<\/figure>\n\n

                  After that, we will have all the conditions together in one single text, with the WHERE keyword:<\/p>\n\n

                  \"\"<\/figure>\n\n
                    \n
                  1. Let\u2019s go back to the main query and edit the SQL statement, and click on the \u201cSource step\u201d<\/strong><\/li>\n<\/ol>\n\n
                    \"\"<\/figure>\n\n
                      \n
                    1. Remove the \u201climit 10\u201d part and Let\u2019s concatenate the existing query with the name of the query that has all the conditions we just defined<\/li>\n<\/ol>\n\n
                      \"\"<\/figure>\n\n
                      \"\"<\/figure>\n\n
                        \n
                      1. Depending on your power query set up, you can get the following error, this is fixed in the Query Options screen<\/strong><\/li>\n<\/ol>\n\n
                        \"\"<\/figure>\n\n

                        Go to file -> Options and Settings -> Query Options<\/p>

                        \t\t
                        \n\t\t\t
                        \n\t\t
                        \n\t\t\t\t
                        \n\t\t\t\t
                        \n\t\t\t\t\t

                        Is your business ready for automation?<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

                        \n\t\t\t\t
                        \n\t\t\t\t\t

                        Automate processes with Microsoft Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

                        \n\t\t\t\t\t
                        \n\t\t\t\t
                        \n\t\t\t\t
                        \n\t\t\t\t\t\t\t
                        \n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
                        \n\t\t\t\t
                        \n\t\t\t\t\t\t\t
                        \n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n\n
                        \"\"<\/figure>\n\n

                        Go to Privacy section and make sure the second option is selected<\/p>\n\n

                        \"\"<\/figure>\n\n

                        After that, error will go away:<\/p>\n\n

                        \"\"<\/figure>\n\n
                          \n
                        1. Close and load your changes and you will see the table in the spreadsheet with the new results:<\/strong><\/li>\n<\/ol>\n\n
                          \"\"<\/figure>\n\n

                          All set! <\/strong>Next time you get a similar request:<\/h3>\n\n
                          \"\"<\/figure>\n\n

                          Just copy and paste the values into the \u201cSales_Orders\u201d table and hit \u201crefresh all\u201d:<\/p>\n\n

                          \"\"<\/figure>\n\n

                          And see new results showing up in SECONDS<\/strong>! 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.<\/p>\n\n

                          \"\"<\/figure>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

                          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 […]<\/p>\n","protected":false},"author":2,"featured_media":2620,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"glossary_letter":"","footnotes":""},"categories":[25],"tags":[13,26,24,16],"class_list":["post-2326","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-power-query-cat","tag-automation","tag-data","tag-data-transformation","tag-excel"],"glossary_letter":null,"rank_math_title":null,"rank_math_description":"Learn to create dynamic SQL queries from Excel using Power Query. Power up your data extraction with this technical guide from Power GI.","rank_math_focus_keyword":null,"_links":{"self":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/2326","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/comments?post=2326"}],"version-history":[{"count":0,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/2326\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/media\/2620"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/media?parent=2326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/categories?post=2326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/tags?post=2326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}