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
Just create a filter like you normally would, then replace the created filter string with the name of your parameter. For example:
= Table.SelectRows(Sales_Customer, each ([TerritoryID] = 10))
becomes
= Table.SelectRows(Sales_Customer, each ([TerritoryID] = varTerritory))
where my varTerritory variable (parameter) is dynamically calculated. It is just another query really that evaluates to a whole number in this case, but could be a date, text, etc.
And note that this may even fold depending on the rest of your queries. This is what Power BI is sending to my SQL server:
select [_].[CustomerID],
[_].[PersonID],
[_].[StoreID],
[_].[TerritoryID],
[_].[AccountNumber],
[_].[rowguid],
[_].[ModifiedDate]
from [Sales].[Customer] as [_]
where [_].[TerritoryID] = 10 and [_].[TerritoryID] is not null
Note that it is sending a 10 but if varTerritory changes, Power Query will change the SQL statement next time it executes.
That works when using query folding.. But I'm writing the query directly, so I can't filter in PowerQuery or I will load the whole table.