Excel 2013 power query - SQL query with a dynamic parameter

Question

however I have looked from the web I cant find a simple(!) example that would show how to use and have a SQL query that would use in the WHERE a dynamic parameter value.

What I'm looking for is that the end user would be able to change a value in the column in the spreadsheet (ParameterTable?) and refresh the data using this new value as a parameter.

Perhaps an example describes best what I'm after.

I would have a table in excel that would have two columns, named ParameterName and ParameterValue. These would then have values PARM1 and 1 (Sorry but appears that I cant post an image here)

The the query could be like this, so if 1=1 retrieve the current date (this is oracle but should be the same for most other databases)

SELECT SYSDATE FROM DUAL WHERE 1 = 1;

How should I change the above so that it would use the ParamaterName and ParameterValue, i.e. that the rightmost "1" could be changed dynamically to any value. If the value would be anything else than "1" no results would be returned.

Thanks in advance

Answer 1

I have another way to pass parameter into Power Query. I make sure it works.

Step 1: Create a table named "Infodesk" in excel, or you can name it as you wish. My table "Infodesk" contains column "Script" where I place the SQL script in this column.

Step 2: Create a blank Power Query, click on Advance Editor, and input following code

let
    Source = Sql.Database("192.168.23.81", "dbo.name", [Query= Excel.CurrentWorkbook(){[Name="Infodesk"]}[Content][Script]{0}])

in
    Source

And then, run your query. The code [Script]{0}] will get data from 1st row, you can place multiple script in the column, then change {0} >> {1} or {2} ... to get data from other rows.

In order to change any parameter in SQL script, you may use the excel function =CONCATENATE() to combine multiple excel cells into a completed SQL.

Answer 2

For anyone who possibly needs:

Create on any sheet a table holding your parameters, say, tParam. Assign a variable:

tParam = Excel.CurrentWorkbook(){[Name="tParam"]}[Content]

Then join it to your database table if you use AND logic:

tResult = Table.Join(tDatabaseSrc, {"Col1", "Col2"}, tParam, {"ColParameter1", "ColParameter2"}, JoinKind.Inner)

This trick won't work if you need OR logic on anything more complex than just ANDs. Then use

tResult = Table.SelectRows(tDatabase, each [Col1] = tParam{0}[ColParameter1] or [Col2] = tParam{0}[ColParameter2])

You can use even more complex conditions, but you need to prepare some data first. For example, rather than using

each [Col1] = tParam{0}[ColParameter1] or [Col1] = tParam{0}[ColParameter2]) [.....] or [Col1] = tParam{0}[ColParameter10])

you can use just

Table.SelectRows(tDatabaseSrc, each List.Contains(ListOfParameters, [Col1]))

Set this ListOfParameters variable in some previous step(s).

In case you use Native Query (what is not the Best Practice, indeed), then maybe you will lose Query Folding. On the other hand, you can load every parameter in a single variable:

tParam = Excel.CurrentWorkbook(){[Name="tParam"]}[Content], tParam1 = tParam{0}[ColParameter1], tParam2 = tParam{0}[ColParameter2], etc etc

, and then just insert them in your query. Again, this is not good, as it is subject to SQL Injection attack, but will do as a fast and non-permanent solution.

Answer 3

I would recommend using filters in this scenario. Your queries would look something like this:

Query ParameterValue:

let
    // This can be created by using From Table and then drilling into a cell
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[ParameterValue]
in
    Source

Query ParameterName:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[ParameterName]
in
    Source

Query that uses the filter:

let
    Source = Sql.Database("server", "database"),
    DatabaseTableStepName = Source{[Schema="SchemaName",Item="ItemName"]}[Data],
    Custom1 = Table.SelectRows(DatabaseTableStepName, each Record.Field(_, ParameterName) = ParameterValue)
in
    Custom1

This uses SQL Server, but it should look similar for Oracle (some of the initial navigation steps will be different).

The last query can also be created by adding a step like this to formula bar once you've chosen the table you want to filter on:

= Table.SelectRows(DatabaseTableStepName, each Record.Field(_, ParameterName) = ParameterValue)

Another way you could do it is to use the Native Query feature, and build the strings dynamically using the & operator. I recommend avoiding this approach if possible because you will get prompted each time you change the parameter and because Power Query will not escape the strings for you if you build the strings dynamically (so you are vulnerable to SQL injection).

Answer 4

Remarked way with Native Query and & was not straight clear for me, so I'd better left here working example

let
    Source = Sql.Database("serverName", "dbName", [Query="select CurrencyCode from tCurrList where CurrencyCode = " & Number.ToText( Excel.CurrentWorkbook(){[Name="CCode"]}[Content]{0}[CCode] )])
in
    Source