Binary timestamp converstion issues

[sgandla]

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

[v-qiuyu-msft]

Hi @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 

[v-qiuyu-msft]

Hi @sgandla,

I've sent a email to consult senior engineers internally. Will update here once I get information. 

Best Regards,
Qiuyun Yu