topic Re: How to Create Date Parameters in Direct query in Developer

How to Create Date Parameters in Direct query

Anonymous — Wed, 28 Aug 2019 17:18:05 GMT

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

Re: How to Create Date Parameters in Direct query

Jayendran — Fri, 30 Aug 2019 07:28:51 GMT

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

Re: How to Create Date Parameters in Direct query

Anonymous — Tue, 03 Sep 2019 16:10:11 GMT

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

Re: How to Create Date Parameters in Direct query

FarmerKenny — Wed, 26 Oct 2022 17:20:56 GMT

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?