Filter SQL query using parameters

[dobregon]

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!

[Do5779]

Have you @dobregon or someone else found a solution yet?

[dobregon]

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

thanks!

[dobregon]

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?

[CNENFRNL]

@dobregon , you may want to refer to this article on passing parameters to sql queries.

https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-...

[edhans]

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]

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]

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]

yes @edhans , i know they are different. 

I dont want a query to calculate the datestart, my datestart changes when depends on what i need in the next refresh in the service. So I'm asking if it is possible to conect the PowerBI parameter to the PowerQuery in order to tupload the report to the server, and for example the next month i think that the startdate should be 2019-01-01 and i only change the paremeter in the service and then the next refresh will use that parameter to take the info in the table.

[edhans]

Yes. Parameters you put in Power Query will show up in the service here for you to manually change.

You would still incorporate those parameters into your query as I showed above. You'd just reverence the parameter vs a query with a date.

[dobregon]

yes, but how can i do the query?

For example, imagine that the parameter in PowerBI is called StartDatePeriod with a value 2020-01-01 and the query that i have to the SQL is the typical

SELECT * FROM dbo.Table

and i want to include the parameter doing something like  

SELECT * FROM dbo.Table where Date>= @StartDatePeriod

I have tried this and it is not working

My idea is then to have something like this in the PowerService

and when i want change to and in the next refresh (in the future) the system will take from 2022

[edhans]

There are only two ways I know of @dobregon 

1. Don't use a SQL Query but rather let Power Query create the query by query folding, which is what I've shown above.
2. Read Chris's blog that @CNENFRNL linked to about passing parameters to SQL queries.

I personnally prefer option 1 as it is 100% dependent on Power BI and reduces coding time - I only have M to deal with, not M and SQL, and subsequent changes in logic won't be impacted.

But I fully understand some are more comfortable with SQL, so the article linked to above would be the way to go.

[dobregon · Accepted solution]

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!

[Anonymous]

Thanks for the parameters (Dates) this is helpful.  Does this really impact the refresh load?  As that is my expectation.  So if my general ledder SQL table has 1,500,000,000 rows I and 10 years of GL data.  I don't want it all and just really need Last three months of the pervious year and the current year.  So two sets of parameters with from and to date each.  Or is there a more lean way to parameterize this. With two datasets for GL data previous year last three months and current year; and then I append one to ther other for a sinble dataset and have one not refresh.  Your thoughts. Please