Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

11 REPLIES 11
MyroslavaM
Advocate I
Advocate I

You can specify the default format of receiving the data for Date format.
Just add en-GB or en-US in the end in the M code.
it prevents from failing the convertation 🙂
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}, "en-GB")

MМ (5).png

fedlov
Advocate II
Advocate II

you need to change locale to TEXT and English-Canada

Jonas1
Helper I
Helper I

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...Skjermbilde.PNG

Anonymous
Not applicable

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,

v-yulgu-msft
Employee
Employee

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

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.
Anonymous
Not applicable

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

sometimes you are just stuck .

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

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.
Anonymous
Not applicable

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. 

Anonymous
Not applicable

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"

Anonymous
Not applicable

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

I had the same issue in PowerQuery where I need to add a date from a parameter to an SQL Statement.

- Date should be a text 'yyyy-MM-dd' wrapped in ' .. '

- value comes from a date formatted parameter

- startDate is the parameter

Solution was:

"some SQL text here ..."&Character.FromNumber(39)&Date.ToText(Date.From(startDate), "yyyy-MM-dd")&Character.FromNumber(39)&" some SQL text here ..."

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.