• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Query from SQL using parameter values from Excel worksheet|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Query from SQL using parameter values from Excel worksheet|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryQuery from SQL using parameter valu…
sp_PrintTopic sp_TopicIcon
Query from SQL using parameter values from Excel worksheet
Avatar
baxbax
Member
Members

Power Query

Power Pivot
Level 0
Forum Posts: 105
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
1
March 13, 2021 - 3:32 am
sp_Permalink sp_Print

Hi,

I am trying to create a query that pulls data from a SQL database. If I create the query by connecting to the SQL database, selecting relevant tables, filtering them, merging them to get what I need it is really slow. I found that if I created the source of the query by entering a SQL query up front then retuning the results I need is significantly quicker.

The issue I have is that I would like to use some parameters in the SQL query. The parameter values would be obtained from cells in the Excel worksheet. These values would be entered by the user to give them what they needed.

The following shows the information I see if I look at the Source line in my query:

= Sql.Database("baxserv", "BaxDB", [Query="SELECT
AA_NOMINAL_TRAN_VIEW.NCODE AS 'Account',
AA_NOMINAL_TRAN_VIEW.DET_NOM_PERIOD AS 'Period',
AA_NOMINAL_TRAN_VIEW.DET_NOM_YEAR AS 'Year',
SL_PL_NL_DETAIL.DET_DATE AS 'Date',
AA_NOMINAL_TRAN_VIEW.DET_TYPE AS 'Type',
AA_NOMINAL_TRAN_VIEW.NNAME AS 'Account Name',
AA_NOMINAL_TRAN_VIEW.HOME_DEBIT AS 'Debit',
AA_NOMINAL_TRAN_VIEW.HOME_CREDIT AS 'Credit',
SL_PL_NL_DETAIL.DET_HEADER_KEY AS 'Header No',
SL_PL_NL_DETAIL.DET_HEADER_REF AS 'Header Ref'

FROM BAXDB.dbo.AA_NOMINAL_TRAN_VIEW AA_NOMINAL_TRAN_VIEW,

     BAXDB.dbo.NL_MAJORHEADING NL_MAJORHEADING,

     BAXDB.dbo.SL_PL_NL_DETAIL SL_PL_NL_DETAIL

WHERE SL_PL_NL_DETAIL.DET_PRIMARY = AA_NOMINAL_TRAN_VIEW.DET_PRIMARY
AND AA_NOMINAL_TRAN_VIEW.NMAJORHEADCODE = NL_MAJORHEADING.NL_MAJORCODE

AND ((AA_NOMINAL_TRAN_VIEW.NCODE IN ('MTR001', 'MTR003', 'MTR004', 'MTR005'))

AND (AA_NOMINAL_TRAN_VIEW.DET_NOM_YEAR = 'C')

AND (AA_NOMINAL_TRAN_VIEW.DET_NOM_PERIOD BETWEEN 1 AND 2)

AND (SL_PL_NL_DETAIL.DET_BATCH_FLAG = 0))

ORDER BY AA_NOMINAL_TRAN_VIEW.DET_PRIMARY", CreateNavigationProperties=false])

The values shown in blue are the ones that I would like to get from the worksheet. 

Any suggestions on the best way to do this will be gratefully received.

Thanks

 

Bax

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 14, 2021 - 12:03 pm
sp_Permalink sp_Print

No idea, sorry, Bax. I dont' know SQL.

I'm surprised the query is faster if you enter your SQL code in the advanced settings, rather than using the Power Query GUI to build the query itself. Using the advanced settings typically breaks query folding, so in theory it should be slower.

Mynda

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
3
March 14, 2021 - 2:14 pm
sp_Permalink sp_Print sp_EditHistory

Hi,

SQL query text is just a text string, use concatenation symbol & just like in any excel string or VBA, you can take data from a table with settings or defined names:

Param1 = Excel.CurrentWorkbook(){[Name="TablenameOrDefinedName"]}[Content]{0}[Column1],
Param2 = Excel.CurrentWorkbook(){[Name="TablenameOrDefinedName2"]}[Content]{0}[Column1],

...

"SELECT IN ('" & Param1 & "', '" & Param2 & "', '" & Param3 & "', '" & Param4 & "')) AND (AA_NOMINAL_TRAN_VIEW.DET_NOM_YEAR = '" & Param5 & "') ORDER BY AA_NOMINAL_TRAN_VIEW.DET_PRIMARY"

Avatar
baxbax
Member
Members

Power Query

Power Pivot
Level 0
Forum Posts: 105
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
4
March 26, 2021 - 3:06 am
sp_Permalink sp_Print

Hi Mynda/Catalin,

I am surprised that the query is faster using SQL. I was really hoping to use PQ logic rather than SQL.

Thanks for the input regarding the SQL syntax. I can get that to work fine if the worksheet cell contains a single value. However as shown in my example the NCODE field has multiple values. This is the nominal ledger account code. I was hoping to be able to enter these in to a list or a concatenated string in the worksheet and get the value from that. Is this possible? In your example it looks like I would have to define each nominal code as a parameter value.

Thanks for your input.

 

Cheers

 

Bax

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
March 26, 2021 - 3:31 am
sp_Permalink sp_Print

You already have the syntax needed to read the text from an excel table cell:

SQLString = Excel.CurrentWorkbook(){[Name="TablenameOrDefinedName"]}[Content]{0}[Column1],

You can build the string entirely in excel then use it in PQ:

= Sql.Database("baxserv", "BaxDB", [Query=SQLString])

Avatar
baxbax
Member
Members

Power Query

Power Pivot
Level 0
Forum Posts: 105
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
6
March 26, 2021 - 10:05 pm
sp_Permalink sp_Print

Hi Catalin,

I am struggling to get this to work. If you look at the attached file you will hopefully see what I am trying to do.

There is a sheet called "Parameter" which has a table showing the values that I would like to pick up and drop in to the SQL query. I have created queries for each of these.

If you look at the Nominal_Query query you will see the SQL. I have managed to drop in the YearParam value in to the string but when I try any of the others I get error messages. 

I tried adding the entire SQL string to a worksheet cell and pulled that in to a query but that gave me errors too.

Thanks

 

Bax

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
March 27, 2021 - 1:57 am
sp_Permalink sp_Print

Unfortunately, nothing you say is reflected in the file: the query is not taken from sheet, only a single parameter is used in the file you attached, YearParam.

And of course, the essential error description is completely missing, I assume you are aware that we cannot test your query against your local database to see what errors are showing.

Please provide detailed description of the errors you receive: screenshots, or error messages. I tried guessing on stars, but I think they was not aligned properly... 🙂

Avatar
baxbax
Member
Members

Power Query

Power Pivot
Level 0
Forum Posts: 105
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
8
March 30, 2021 - 1:25 am
sp_Permalink sp_Print

Hi Catalin,

I was not trying to demonstrate to you the error messages I am receiving I am aware that you will not be able to connect to the database.

What I am trying to show in the file is how I have created the parameter fields. I have input them in a table called "Table_Parameters" in the Parameter worksheet. I then created connection only queries from this table. For each line in the table I created a query and then drilled down to give me either an alpha or numeric value. The following shows the queries that exist in the example file.

[Image Can Not Be Found]

I then tried to use the SQL syntax you gave me in your first answer to substitute these value fields in to the code. So for the Nominal year the line in the SQL code shows (AA_NOMINAL_TRAN_VIEW.DET_NOM_YEAR='" & YearParam & "') and this works fine. I then tried to substitute in the start and end periods. The SQL code as standard for this reads, (AA_NOMINAL_TRAN_VIEW.DET_NOM_PERIOD Between 1 And 2). I tried the following substituting in the query values, (AA_NOMINAL_TRAN_VIEW.DET_NOM_PERIOD Between '" & StartPeriodParam & "' And '" & EndPeriodParam & "'). I then get an error message saying "Expression.Error: We cannot apply operator & to types Text and Number.". Finally I tried using the AccountsParam. In the table there are multiple values in the cell for this separated by commas. I was hoping that I could drop this in as a parameter so if I need to change the values I just need to update the worksheet cell. Again I get an error, "DataSource.Error: Microsoft SQL: Incorrect syntax near 'MTR001'."

My question at this point is is the method I have used to create the parameters from the worksheet table correct? Then is the syntax I am using to try and drop these in to the SQL code correct? Finally can I have a cell with multiple values like the Accounts one and use this in the SQL? Am I just doing it all wrong?

Thanks

 

Bax 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
March 30, 2021 - 3:16 am
sp_Permalink sp_Print

Much clear then before.

"Expression.Error: We cannot apply operator & to types Text and Number."  That happens because StartPeriodParam is a number and PQ can only join text strings. Simply use :

(AA_NOMINAL_TRAN_VIEW.DET_NOM_PERIOD Between '" & Text.From(StartPeriodParam) & "' And '" & Text.From(EndPeriodParam) & "')

Note that the line above will return a query text like this:

(AA_NOMINAL_TRAN_VIEW.DET_NOM_PERIOD Between '1' And '2'), you might not need to add the single quotes.

The single quotes are optional when the string value does not contain any space, If there is any space in the string value, it will throw an error - invalid syntax.

You can build the query text any way you prefer, completely in excel cells or partially in excel cells, at the end it will all be a piece of text, so it does not really matter how you join the pieces.

"DataSource.Error: Microsoft SQL: Incorrect syntax near 'MTR001'."

As suggested above, use separate steps:

SQLString = BuildYourQueryTextHereToBeUsedInNextStep,

LaunchQueryToDB= Sql.Database("baxserv", "BaxDB", [Query=SQLString])

You will be able to inspect the query text if you select the SQLString step, any error can be easily identified.

Once the text looks like the query that you know to be functional, you will be able to run it.
Try with space instead of #(lf).

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Ben Hughes, Francis Drouillard
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.