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
Jonas1's avatar
Jonas1
Icon for Helper I rankHelper I
7 years ago

Hi,

 

I have the same problem as Anonymous 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...

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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,