cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamTrexler Helper IV
Helper IV

Need help converting datetime to text

I am using DateTime.ToText (in the M language) in a custom column to convert a datetime to text and concatenate it to the value in  another column, and I am having trouble getting it into a format that is useful for my report. The M language reference for the function says to check the Library Specification, but the latest version of this document I can find (Feb. 2015) only gives a few format elements - none of which can apparently handle a 24-hour format for the hour portion. Testing it out, the "hh" portion is always 01-12, with 12:00 being either midnight or noon and 01 being either 1:00 AM or 1:00 PM.

 

The problem is that I need to compare this to a text column generated by SQL Server, which has only a few, fixed formatting options as well. The most appropriate ones for my report use a 24-hour clock. So I am trying to create soemthing like "yyyy-mm-dd hh:mi:ss" (the "ODBC canonical" format in SQL Server), where "hh" would be 13 for 1:00pm. Even the format "yyyy-mm-ddThh:mi:ss.mmm", which appears to match the ISO8601 format in SQL Server, only uses a 12-hour clock.

 

Is there a simple way to do this without having to parse it into its components and re-assemble it from individual pieces of text?

 

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
SamTrexler Helper IV
Helper IV

Re: Need help converting datetime to text

I found the solution, so I'm posting it here in hopes it may help someone else.

 

Using "yyyy-mm-dd HH:mm:ss" did the trick - capitalizing the HH create a 24-hour clock, from 00-23. So my formula reads DateTime.ToText([KeyDateTime],"yyyy-mm-dd HH:mm:ss") & " " & [UnitId] and this compares correctly to the SQL Server output. I can even create a relationship using this column.

 

It's odd that this is not documented anywhere I could find it, in this forum or the MSDN doc or even with a Google search. And I've run into this with other functions as well, where the documentation refers to elements but there is no specification for those elements. I respect the fact that Power BI Desktop is growing quickly, and I love that, but surely such simple parts of the language such as format strings must be documented somewhere?

View solution in original post

SamTrexler Helper IV
Helper IV

Re: Need help converting datetime to text

Actually, the formula should read "DateTime.ToText([KeyDateTime],"yyyy-MM-dd HH:mm:ss") & " " & [UnitId]" - who would have guessed that "MM" means month and "mm" means minute? It really should be documented somewhere!

View solution in original post

2 REPLIES 2
SamTrexler Helper IV
Helper IV

Re: Need help converting datetime to text

I found the solution, so I'm posting it here in hopes it may help someone else.

 

Using "yyyy-mm-dd HH:mm:ss" did the trick - capitalizing the HH create a 24-hour clock, from 00-23. So my formula reads DateTime.ToText([KeyDateTime],"yyyy-mm-dd HH:mm:ss") & " " & [UnitId] and this compares correctly to the SQL Server output. I can even create a relationship using this column.

 

It's odd that this is not documented anywhere I could find it, in this forum or the MSDN doc or even with a Google search. And I've run into this with other functions as well, where the documentation refers to elements but there is no specification for those elements. I respect the fact that Power BI Desktop is growing quickly, and I love that, but surely such simple parts of the language such as format strings must be documented somewhere?

View solution in original post

SamTrexler Helper IV
Helper IV

Re: Need help converting datetime to text

Actually, the formula should read "DateTime.ToText([KeyDateTime],"yyyy-MM-dd HH:mm:ss") & " " & [UnitId]" - who would have guessed that "MM" means month and "mm" means minute? It really should be documented somewhere!

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors