Issue passing variable to SQL Query

(0) Share

Report

Posted on by Stuart74

118

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

Categories:

Power Query

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Stuart74 118 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Stuart74 118 on at

Like (0)

Report
Copy link

Link copied!

Hello

Thank you for your suggestions.

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

Was this reply helpful? Yes No