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.
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
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
Hi @sgandla,
I've sent a email to consult senior engineers internally. Will update here once I get information.
Best Regards,
Qiuyun Yu
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |