Forum Discussion

dobregon's avatar
dobregon
Impactful Individual
5 years ago
Solved

Filter SQL query using parameters

Hi guys,

 

I want to take values from a table in my SQL server but i want to filter the query acording a parameter.

 

Imagine that i have a million of rows about daily values of customers and i want to have 2 parameters (startdate and enddate), so i want to take the dable doing something 

SELECT * FROM TABLE WHERE DATE>=STARTDATE AND DATE<ENDDATE 

 

But it seems that i can't use parameters to do that, is it possible?

Kind regards!

Need Help
  • dobregon's avatar
    dobregon
    5 years ago

    Thanks edhans 

     

    In reality, that i didnt understand is what simply was to put the same name than the parameter in the powerquery and this will filter the query... 

    i just find the new video from  Patrick that it is very visual to see what i'm looking for and for future visitors to this topic.


    Thanks edhans  and CNENFRNL  for your support!



13 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    It depends on how you are setting those values. I use queries as a scalar value for this. So I might have a query that simply evaluates to = #date(2021,3,1), then I have a simple query like this:

    let
    Source = Sql.Databases("localhost"),
    ContosoDW = Source{[Name="ContosoDW"]}[Data],
    DaxBook_Sales = ContosoDW{[Schema="DaxBook",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(DaxBook_Sales, each ([Order Date] >= varStartDate))
in
    #"Filtered Rows"

    Power Query will then fold that and send this SQL statement to the server for processing:

    select [_].[OnlineSalesKey],
    [_].[StoreKey],
    [_].[ProductKey],
    [_].[PromotionKey],
    [_].[CurrencyKey],
    [_].[CustomerKey],
    [_].[OrderDateKey],
    [_].[DueDateKey],
    [_].[DeliveryDateKey],
    [_].[Order Date],
    [_].[Due Date],
    [_].[Delivery Date],
    [_].[Order Number],
    [_].[Order Line Number],
    [_].[Quantity],
    [_].[Unit Price],
    [_].[Unit Discount],
    [_].[Unit Cost],
    [_].[Net Price]
from [DaxBook].[Sales] as [_]
where [_].[Order Date] >= convert(datetime2, '2021-03-01 00:00:00')

     

    varStartDate (you can call it whatever you want) can be hard coded, dynamic based on today's date - =DateTime.Date(DateTime.LocalNow()), or any other date logic. It can also be based on values in another query, so 

    = List.Min(
    List.Buffer(SomeQuery[Order Date])
   )

    Would return the earliest date from the order date field of SomeQuery. You could further manipulate that with Date.StartOfYear, etc.

    • dobregon's avatar
      dobregon
      Impactful Individual
      5 years ago

      Thanks edhans and CNENFRNL  for your replies. 

      I know how to create parameters int he SQL or filter in the table, but what i want is to create 2 parameters in the PowerBI (startdate and enddate) and then filter the SQL query / Power Query related to that parameters

       


      My idea (if it is possible) is to create both paremeters with short dates, upload the PowerBI to the service and then change the startdate to 2020-01-01 and then refresh the dataset. This is what I'm asking, the possibility to read that type of powerbi parameters in the PowerQuery.

      is it possible?

      Regards!

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        My point dobregon is you are thinking SQL parameters and Power BI parameters are the same thing. They are not. My example above shows you code how to pass the start/end date variables to your data. Your StartDate could be something like this:

         

        = Date.StartOfYear(Date.AddYears(DateTime.Date(DateTime.LocalNow()),-3))

         

        Today that will generate Jan 1, 2018, and will cause your SQL query see it as :

         

        where [_].[Order Date] >= convert(datetime2, '2018-01-01 00:00:00'

         

        when PQ passes the date if you use it like I showed above.

         

        You can use the dates as parameters like you've shown, but they will not be dynamic. You have to go to the service to change them. Certianly possible, but I usually reserve those parameters for database and server names. My start/end dates need to adjust themselves over time.

  • dobregon's avatar
    dobregon
    Impactful Individual
    5 years ago

    I have opened again if someone can help me on this. The other solution is valid to simple queries, but now i have a query to an SP in SQL

    DECLARE @dateFrom VARCHAR(10) = DATEADD(DAY,1,EOMONTH(GETDATE(),-2))
DECLARE @dateTo VARCHAR(10) = NULL

EXEC [bi].[SP_Values]
@dateFrom_input = @dateFrom
,@dateTo_input = @dateTo


    As you can see i need to send values to the parameters in the SP. That i want is to create parameters in the PowerBI and call them like

    DECLARE @dateFrom VARCHAR(10) = ParameterStartDate
DECLARE @dateTo VARCHAR(10) = ParameterEndDate

EXEC [bi].[SP_Values]
@dateFrom_input = @dateFrom
,@dateTo_input = @dateTo


    is this possible?

    • dobregon's avatar
      dobregon
      Impactful Individual
      3 years ago

      sorry i dindt accept the solution. it is on the video.

      thanks!