Forum Discussion
Dynamic parameter for SQL to filter last year
Hi I need to pass a dynamic parameter to an SQL query, so I can get the last year, but filtered in the SQL code. Actually, I'm using a fixed parameter, with today's day and month. But how can I...
Finally, I've solved it in this way:
let //First, create the variable in PowerQuery Startdate=Text.From(Date.Year(Date.AddMonths(DateTime.LocalNow(),-12))), StartMonth=Text.PadStart(Text.From(Date.Month(Date.AddMonths(DateTime.LocalNow(),-12))),2,"0"), YearMonth=Startdate & StartMonth, //Then use the variable in the SQL Source = Sql.Database("ServerName", "DatabaseName", [Query=" Select * from TABLE_NAME Where YEAR_MONTH='"&YearMonth&"' "]) in Source
Finally, I've solved it in this way:
let
//First, create the variable in PowerQuery
Startdate=Text.From(Date.Year(Date.AddMonths(DateTime.LocalNow(),-12))),
StartMonth=Text.PadStart(Text.From(Date.Month(Date.AddMonths(DateTime.LocalNow(),-12))),2,"0"),
YearMonth=Startdate & StartMonth,
//Then use the variable in the SQL
Source = Sql.Database("ServerName", "DatabaseName", [Query="
Select *
from TABLE_NAME
Where YEAR_MONTH='"&YearMonth&"'
"])
in
Source
Anonymous by doing it that way you have created a SQL statement that will prevent further folding.
If you create a blank query and then put your yearmonth logic in it to return an integer (202001 for example), then refer to it in a query as I displayed, additional steps in your query will continue to fold.
By not allowing folding to continue, it will pull in EVERY column for that table and 100% of any further transformations will be done on the client or the gateway.