Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

M - Date Format YYYY-MM-DD in Query while sending request to an API

Hi Community,   I need help with yet another date formating issue where I am struggling. I am wokring on a web API call from PBI, where I have to provide start and end dates as part of my query. ...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous,

 

You can use this M code to get date formatted as "YYYY-MM-DD".

Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")

But, the result is recognized as string. If we change it to date type, it will be automatically formatted to "D/MM/YYYY". Based on my test, Power Query only supports date format with "/" as delimiter. If date values contain "-", it will be changed to string by default.

 

Regards,
Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks v-yulgu-msft yes I have tried this today, and as expected, API rejects it :(

sometimes you are just stuck .

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    Unfortunately, Power BI doesn't support date format 'YYYY-MM-DD'. Would it be possible to change the API suppoted date formate to 'YYYY/MM/DD'?

     

    Regards,
    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      I have tried but API rejects it as will only accept that one format and that too in .number format.

      I would try to get in touch with the API technical team to see, if they can create some provisions. Not sure how hard would it be.