Issue passing variable to SQL Query

[Stuart74]

Hello

I am writing a Power Query to extract data from a SQL Database.  I wish to filter the data extracted.

I have tried one step being the SQL query, and then the second step the filter, but that ends up with the Power Query loading all rows from the database before filtering, or thats what it looks like.

So I then tried referencing a variable/parameter in the SQL step.  This is the bit I need help with.

The variable is a value from the a sheet,

But then I get this when I try to use

Please does any one have any advice?

Thank you

Stuart

[Syndicate_Admin]

Hi,

Power Query won't actually import all of the data then later filter it with your first implementation, if performed correctly. PQ will actually fold your subsequent filter back to the server as a native query if correctly structured.

You'll need to provide an example of your SQL table structure and what you're trying to achieve to get a more accurate answer, but your query to the SQL source should look something like this:

let
    Source = Sql.Database("SERVER", "DATABASE"),
    Data = Source{[Schema="SCHEMA",Item="TABLE"]}[Data],
    Filter = Table.SelectRows(Data, each [APStart] >= Parameters[APStart]
in
    Filter

Pete

[Syndicate_Admin]

If you wish to create a dynamically changing SQL statement based on parameters in PowerQuery, I'd recommend essentially the following: 

First, create a function to execute a SQL query, then import the SQL text from a .sql or .txt (really whatever you prefer), use the Text.Replace function to replace a placeholder in your code (For example, supposing your text was:

SELECT *
FROM DB.Table
WHERE ConditionColumn = [DYNAMICCHANGINGVALUE]

You'd run Text.Replace on that filtering [DYNAMICCHANGINGVALUE] and substituting your parameter,
and then run the query on your SQL in a blank query.  

This would allow you to dynamically run what you'd like.  The only limitation that I've found is that if you want this to automatically run on the service, you need a specific gateway configuration, but if you're doing this in desktop, you shouldn't have problems.  Hope this helps!  

If you want more details I can probably provide those as well, just don't know how much is useful.

[Stuart74]

Hello

Thank you for your suggestions.

I have made some changes and have shown below.  This runs, returns the right result, but I am unsure if it works correctly.

Parameters (GetVar)

Code;

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Input")
in
Value

Query with SQL

I let it run for 15 mins, it counted to 15million plus rows, then loaded 884k rows.  So the right result, BUT;

Please can you advise is this is working correctly?  The query in SQL Mgmt Studio takes about 50 seconds with the same parameters.

Thanks

Stuart

[Syndicate_Admin · Accepted solution]

This will work a thousand times faster if you fix the real problem, which is that your parameter isn't a value, it's a table. You should right click then drill down on the value to make it just an integer value by itself. Then the name of this query is the variable name containing your value. Do that with your APStart and APEnd Queries. So if you are in the advanced editor, you would amend the SQL add this before your final quotation mark ("), AKA the end of your WHERE statement:

AND AccountingPeriod >= "&ApStart&" AND AccountingPeriod <= "&APEnd&

Play around with that. My point is that if your variable is an actual value, you can refer to it in the SQL using the "&QueryName&" syntax. Folds right into the SQL.

--Nate

[Stuart74]

Hello

Thank you for your suggestions.

I did what you suggested and it worked.  Thank you.