cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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.

The format the API takes is YYYY-MM-DD ( as numeric string).

It is working fine when I am giving hard coded dates in my query as follow: 

start_date = ""2017-07-01"",

end_date=""2017-08-15"",

 

The issue starts when I try to automate this, as I have to provide the latest date in the query, if I do not want to edit the query on daily basis.

 

The only function I knew was, 

DateTime.Date(DateTime.LocalNow())

  gives an output in DDMMYYY. 

Are there any tricks or function that I can use to change my input query dates to format YYYY-MM-DD(as numeric string)

I have even tried to input these dates as a paramter , while storing the dates in a seperate list , but apparently even that is not working. I get error from API, -> API only accepts dates in format YYYY-MM-DD.

 

Thanks in Advance.

 

 Added Later: I ave tried to get the components from Date.Day, Date.Month and join them back , but that needs it to be chaneged to text. and API doesn't accept texts 😞

let

today = DateTime.Date(DateTime.LocalNow()),
day= Number.ToText(Date.Day(DateTime.LocalNow())),
month = Number.ToText(Date.Month(DateTime.LocalNow())),
year = Number.ToText(Date.Year(DateTime.LocalNow())),
out = year&"-"&month&"-"&day,
out2 = Number.FromText(out)
in
out2

 

8 REPLIES 8
Highlighted
Community Champion
Community Champion

Re: DAX - Date Format YYYY-MM-DD in Query while sending request to an API

In DAX you can use a function called FORMAT.  Put the date column as the first parameter, then use this (including quotes) as the 2nd parameter: "YYYY-MM-DD"

Highlighted
Helper V
Helper V

Re: DAX - Date Format YYYY-MM-DD in Query while sending request to an API

@Ross73312 Apologies for incorrect header, as I am trying to do it in M , not in DAX. Because I have to provide the query parameters in advanced editor .(Is there a way I can use DAX in there?)

Highlighted
Microsoft
Microsoft

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

Hi @emudria,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper V
Helper V

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

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

sometimes you are just stuck .

Highlighted
Microsoft
Microsoft

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

Hi @emudria,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper V
Helper V

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

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. 

Highlighted
Helper I
Helper I

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

Hi,

 

I have the same problem as @emudria had. Did you solve it Emudria? Or does anyone else know how to get relative date formulas on this exact format in M? 

 

So far I have made an API request starting with what you find in the attachment. I can only get it working with fixed dates though...Skjermbilde.PNG

Highlighted
Helper V
Helper V

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

Hey @Jonas1 

 

I was able to solve it as follow:

we can set the end date a dynamic date, as you have given in your example

enddate = DateTime.Date(DateTime.LocalNow())

here, we have got the date what we want to input to pur query.

But problem is, the API doesnt accept date in same format as we get form Datatime.Date as well as my API wanted data in string format only.

 

so once I would get a value stored in enddate as mentioned above, 

I have changed it into a string manually as follow (you don't need to create variable today, I am just being lazy and copying my code[but even if you do, that wont harm])

enddate = DateTime.Date(DateTime.LocalNow()),

today= End_Date,
year = Number.ToText(Date.Year(today)),
month = Text.PadStart(Number.ToText(Date.Month(today)),2,"0"),
day = Text.PadStart(Number.ToText(Date.Day(today)),2,"0"),
mydate = year&"-"&month&"-"&day,

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors
Users online (905)