Forum Discussion
Impactful IndividualFilter SQL query using parameters
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!
Impactful IndividualThanks 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!
Community ChampionMy 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