DataFormat error while trying to convert to DateTimeZone

[Anonymous]

Hi,

I'm trying to get data from Azure Cosmos DB with mongoDB API as a data source.

One of the fileds in the json record is 'time' with BSON Date type as mongoDB treats date types.

The Date value of the record in mongoDB looks like this:

which is:

"time" : ISODate("2019-03-01T05:41:39.000Z")

Atfter clicking on 'Refresh Preview' and expanding my document I'm getting a record value in 'time' field:

Containing this value:

When trying to refresh power bi I'm getting this error:

DataFormat.Error: We couldn't convert to DateTimeZone.
Details:
1.55142E+12

I tried to change the type for this column to any of these types Date/DateTime/DateTimeZone and it didn't work.

Can some help me with getting this value formated as DateTime / DateTimeZone type?

Thanks.

[v-juanli-msft]

Hi @Anonymous 

The final result (1.55142E+12) shows when you click on the "Record" field?

Best Regards

Maggie

[Anonymous · Accepted solution]

Hi @Maggie ,

The value I got is a BSON Date which is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). 

I managed to overcome this failure by adding a custom column with this calculation:

Table.AddColumn(#"Expanded time", "time", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[#"$date"]/1000))

worked fine.

Thanks.