# Calculating date from Unix Timestamp in Power Query

• ### Question

• So, I'm trying to analyse my Google Location History. I'm loading from the Google export file, through Power Query, into a Power Pivot data model.

The file contains the datetime of each locational fix as a integer. This integer represents the milliseconds since the epoch, which is 1970-01-01:00:00:00.

In SQL, this would be straight trivial. I'd get rid of the milliseconds, and do this:

select

and get

2014-09-16 22:50:51.000

So, basically, I have the seconds value in the table, and I'm 'hardcoding' the epoch, but that's fine.

I'm sorry if I'm being stupid, but how on Earth do I do this in M in Power Query?

Tuesday, November 11, 2014 9:56 PM

• #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, 1410907851)

• Proposed as answer by Tuesday, November 11, 2014 9:59 PM
• Marked as answer by Tuesday, November 11, 2014 10:05 PM
Tuesday, November 11, 2014 9:58 PM

### All replies

• #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, 1410907851)

• Proposed as answer by Tuesday, November 11, 2014 9:59 PM
• Marked as answer by Tuesday, November 11, 2014 10:05 PM
Tuesday, November 11, 2014 9:58 PM
• Absolutely sensational. Thanks very much Curt, I was a very long way away from working that our for myself.
Tuesday, November 11, 2014 10:05 PM
• Is it possible to do this in reverse and change a date formatted as:

 21-Jun-17 13:20:42.34700

to Unix Epoch time in seconds with milliseconds after the decimal?

Thursday, June 22, 2017 8:43 PM
• Hi Brendan,

One of possible ways is

`Number.Round(86400*(Number.From("YourDateTimeValue")-Number.From(#datetime(1970,1,1,0,0,0))),3)`
Regards

Friday, June 23, 2017 11:06 AM
• When I sub in my Date/Time column for "YourDateTimeValue" I get the following error:

DataFormat.Error: We couldn't convert to Number.
Details:
21-Jun-17 13:39:22.77802

is there a workaround for this?  I have the type set to "Text" because if I change it to Date/time I lose the milliseconds.

Monday, June 26, 2017 3:27 PM
• You do not lose your milisecons... You can't see it only.
Please, select one "cell" with your data... bottom in the PQ window you can see what is exactly in this cell.
Monday, June 26, 2017 8:11 PM
• I have added a custom column with the following:

Number.Round(86400*(Number.From([Time])-Number.From(#datetime(1970,1,1,0,0,0))),3)

"Time" is the name of my date/time column.  In the new column it just says Error, but doesn't show any value.  When I click on the individual cell I see the following error in the table viewer:

DataFormat.Error: We couldn't convert to Number.
Details:
21-Jun-17 12:57:59.67201

Do you see anything I am doing wrong that will explain this?

Tuesday, June 27, 2017 3:26 AM
• Try this code below (put it in advanced editor) and look at the result.
This solution is more precise than previous one.

```let
Source = Table.FromColumns({{"21-Jun-2017 13:39:22.7780200", "7-Oct-1998 06:02:39.0580150", "21-Jun-17 12:57:59.67201"}}, {"Time"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "DateTimeFromSeconds", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [#"Seconds(Unix)"])),
in

In my example (code above) my start data (source) are text ,  then i change the type of "Time" column.

The most interested part ( for You) is
` Duration.TotalSeconds([Time]-#datetime(1970,1,1,0,0,0))`
This is what you need.

Hope, everything is clear now :-))

Regards

• Edited by Tuesday, June 27, 2017 11:03 AM
Tuesday, June 27, 2017 11:00 AM
• Thank you very much!  This works perfectly.

Brendan

Tuesday, July 4, 2017 11:54 PM