Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 calculate the parameter instead of having a fixed value?

I've seen some post where they calculate the field and then use reduce rows, but I need to use it in the SQL code.

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    In theory you could use a query parameter and have it be the SQL code to evaluate. Then in your SQL query/stored procedure/etc. just wrap an EVALUATE around that parameter coming in?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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
    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      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.