Need help converting datetime to text

[SamTrexler]

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.

[SamTrexler · Accepted solution]

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?

[SamTrexler · Accepted solution]

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?

[SamTrexler · Accepted solution]

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!