Forum Discussion
Binary timestamp converstion issues
Hi All,
My xml has Date format in the form of 6.35912E+17 , When i import xml in Power BI Desktop and try to change the Date format as [mm/dd/yyyy] it gets converted to Error.
Within the XML data pack file for the Loop Performance channel, the interval elements contain time stamps that are represented as an integer value.
<interval id="0" starttime="635912352010000000" endtime="635912639960000000" aggregationtype="None" />
<interval id="1" starttime="635912064030000000" endtime="635912351990000000" aggregationtype="None" />
<interval id="2" starttime="635911776020000000" endtime="635912063980000000" aggregationtype="None" />
I ran the following PowerShell command on all of the values:
>> [DateTime]::FromBinary(VALUE).ToLocalTime().ToString("o")
Here is the list of resulting time stamps printed to ISO 8601 format.
Start End
2016-02-16T11:00:01.0000000-05:00 2016-02-16T18:59:56.0000000-05:00
2016-02-16T03:00:03.0000000-05:00 2016-02-16T10:59:59.0000000-05:00
2016-02-15T19:00:02.0000000-05:00 2016-02-16T02:59:58.0000000-05:00
how will we convert to the date datatype setting in PBI Desktop.
Thank you
2 Replies
Hi sgandla,
I've sent a email to consult senior engineers internally. Will update here once I get information.
Best Regards,
Qiuyun YuHi sgandla,
There’s isn’t a built-in way to do that. This .NET-specific serialization format is pretty complicated but you could look at its source code here and try to reimplement the logic as an M function.
For simple cases like this one, where there’s no timezone embedded, you can say something like
let
ConvertTimestamp = (ts) => #datetime(1, 1, 1, 0, 0, 0) + #duration(0, 0, 0, ts / 10000000)
in
ConvertTimestamp(635912639960000000)Best Regards,
Qiuyun Yu
