14-digits Timestamp wrongly converted

[Anonymous]

Hi community!

I have been transforming some data in my Power query to create some reports that I need for the univeristy, but I'm struggling trying to convert and 14-digits timestam to datetime:

1. I have these kind of records:

20170801000026, which basically is YYYY-MM-DD HH:MM:SS.

2. I created a new custom colum whith this custom formula: 

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Tiempogps]/1000)

(https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/m-p/130...)

3. The new column is un datetime format, but in a weird way, because this is showing me a wrong date for every record:

For example, in the previous example my original datestamp was 20170801000026 and a was expecting something like 2017-08-01 00:00:26 but system is showing me 2609-03-10T08:16:40.0260000.

Maybe I'm making some step wrong or I have a wrong column format but I tried multiple formats and I can't get the correct datetime.

I'll aprecciate if you can help me! Thanks in advance.

[Syndicate_Admin]

Awesome!!

[collinsg]

A quick way of doing this is to use DateTime.FromText. First change the type of the 14 digit timestamp to text. Then use the format option in the second argument of DateTime.FromText. The formats are described here.

= Table.AddColumn(
   #"Name of Previous Step", 
   "Date & Time", 
   each DateTime.FromText( [14 Digit Timestamp], [Format="yyyyMMddHmmss"] ),
   type datetime
)

[MattAllington]

It doesn't look like Unix time to me. It looks like a surrogate DateTime key. Split the components of time into year, month, etc, then join back with the date and time functions. 

[Fowmy · Accepted solution]

@Anonymous 

Add this custom column, you can do the formatting after loading the query to the model as you need

=Date.From(Text.Start([Timestamp],8))&Time.From(Text.End([Timestamp],6))

[Anonymous]

It works perfectly my friend! Thank you so much! 😄

[Anonymous]

I bet Column From Examples would work here.

--Nate