Log In
Register
Home
Power Query
Power Pivot
July 11, 2016
Offline
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
July 16, 2010
Offline
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
November 8, 2013
Offline
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"
Power Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
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])
Power Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
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... 🙂
Power Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
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).
1 Guest(s)
