Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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?
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!
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?
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |