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

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"

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

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])

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

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

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 

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