Forum Discussion
How to Create Date Parameters in Direct query
Hi Team,
I'm want to restrict the data to the users. So, i have created a Date parameter wherein user have to select Start date and End Date in Power bi service.
I have created below M Langauage and pasted in Advance Editor
Next I have Created two Parameters Param1 and Param2
let
SQLSOURCE=(Param1 as date, Param2 as date)=>
let
Param1=Date.ToText(Param1,"MM")&"/"&Date.ToText(Param1,"dd")&"/"&Date.ToText(Param1,"yyyy"),
Param2=Date.ToText(Param2,"MM")&"/"&Date.ToText(Param2,"dd")&"/"&Date.ToText(Param2,"yyyy"),
Source=Sql.Database("sqllink","Database",
[Query="Select Column1,Column1,Date_Txt from (SELECT Column1,Column2,LEFT(CONVERT(VARCHAR, Date_N, 120), 10) as Date_Txt FROM dbo.Transform )a
where Date_Txt >='"&Param1&"' AND Date_Txt <='"&Param2 & "'"])
in
Source
in
SQLSOURCE
When i run the query i am not getting any results.
Please guide me how to create parameters in date level and add in power bi dataset.
Thank you
Ravi
3 Replies
Hi Anonymous ,
Basically it should work. Do you have tried this with the latest PBI Desktop ?
Here is the real working sample video : https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI
Hi Jayendran,
Thank you for your reply on this...
I Have created parameters as showed in your video it is working in Power Bi desktop. However, when i go to Dataset -- Settings -- Parameters. Over there in StartDate and EndDate i have give date range. As per the given date range data is not populating in power bi service. My i know the reason... In Desktop it is working fine but when comes to PBI Services why data is not populating as per given range....
Please clarify..
Thanks in adavance.
RAVIP
I passed the exact syntax to my SQL query for the data parameter, '"StartTime"' and I received a token Comma syntax error in the Advanced Editor. Both the Parameters are Date data types. Any ideas?
