How to Create Date Parameters in Direct query

[Anonymous]

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

[Jayendran]

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

[FarmerKenny]

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?

[Anonymous]

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