Forum Discussion

MichaelShown's avatar
MichaelShown
Frequent Visitor
3 years ago
Solved

How to use date parameters in power query

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 corre...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

MichaelShown's avatar
MichaelShown
Frequent Visitor
3 years ago

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's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.