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"",
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,
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 😞
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
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"
@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?)
You can use this M code to get date formatted as "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.
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'?
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.
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...
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()),
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,
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!