How to use date parameters in power query

[MichaelShown]

Hello,

I'd like to know if a date (e.g. YYYY-MM-DD) can be used as one of parameters in power query of Power BI desktop.

As shown below, when adding actual dates in query, the system shows correct results.

However, when using date parameters in the query, the system does not show any results.

 Does anyone know why date parameter is not working?

(FYI, data connectivity mode is 'DirectQuery'.)

 Any comments will be very appreciated.

 Thank you.

[MichaelShown]

I tried your suggestion but as shown below, I'm getting the error message : 

"Expression.Error: We cannot apply operator < to types Date and DateTime"

Any comments will be appreciated.

[v-jingzhang · Accepted solution]

Hi @MichaelShown 

& is used to concatenate text strings. Your parameters S_DATE and E_DATE are both of date type, so one solution is to change both of them into Text data type. 

Another option is to use Date.ToText or Text.From function to convert the parameter values to text type in the code directly:

"... Start_Date Between '" & Date.ToText(S_DATE, [Format="yyyy-MM-dd"]) & "' AND '" & Date.ToText(E_DATE, [Format="yyyy-MM-dd"]) & "' AND ... "

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ppm1]

Try this instead.

"... Start_Date Between '" & S_Date & "' AND '" & E_Date & "' AND ... "

Pat

[rnola16]

As a solution : When creating a new parameter, define the Type as Text and then use '"&S_Date&"'. This syntax works.