cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emudria Member
Member

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
Ross73312 Super Contributor
Super Contributor

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"


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


emudria Member
Member

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?)

Microsoft v-yulgu-msft
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.
emudria Member
Member

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 .

Microsoft v-yulgu-msft
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.
emudria Member
Member

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. 

Jonas1 Regular Visitor
Regular Visitor

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

emudria Member
Member

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors