DateTime in Azure Cosmos DB

[BenKoth]

I'm using an Azure Cosmos DB that is populated through the Azure Cosmos .NET SDK. According to the documentation, DateTimes should be stored as a serialize string in ISO_8601 format (https://docs.microsoft.com/en-us/azure/cosmos-db/working-with-dates). When I view my records in the azure portal or the azure storage explorer app, it appears like that is the case.

You can see my StartTime and EndTime properties as well as the TimeStamp are in the format "2019-07-15T16:24:51.392Z"

When I import the data to Power BI Desktop, the value comes out as some bizarre string/number. e.g. 

00636988010188048801

I thought that perhaps it was the # of milliseconds since 1/1/1970, but that is not the case. Does anyone know how to get the date time imported into Power BI correctly or how to convert that value to a datetime? Trying to just set the data type to any of the various DateTime options results in an error.

[v-diye-msft]

Hi @BenKoth 

You can refer to this article to convert ISO format to date , and i have do a pbix for to to try.

Reference:https://community.powerbi.com/t5/Desktop/Convert-isoYearIsoWeek-YYYYWW-into-a-date/m-p/148556/highli...

here is pbix, the steps are in it.

https://www.dropbox.com/s/u8nbcwpno4ld1h9/Convert%20ISO%20%28WWYY%29%20format%20to%20a%20date.pbix?d...

[BenKoth]

Thanks. I don't think my data is actually in the ISO format like it claims. I don't know what format it is. Has anyone used the Azure Cosmos DB to get data that contains a date time?

My data is populated using the Azure Cosmos Table SDK, I wonder if that is where the disconnect is. You have to do some strange URL manipulation to change the table.cosmos to "documents" for the datasource URL.

[BenKoth]

I had submitted a similar question on the Azure Cosmos Table SDK Github page and it looks like they are purposely storing this date in an unknown format. I'm guessing they aren't going to change the format so I'm going to submit an issue to Power BI to add the ability to convert this format to DateTime. For reference:

https://github.com/Azure/azure-cosmos-table-dotnet/issues/14

[BenKoth · Accepted solution]

I was able to get a solution from the issue/idea I submitted.

let

ConvertTimestamp = (ts) => #datetime(1, 1, 1, 0, 0, 0) + #duration(0, 0, 0, ts / 10000000)

in

ConvertTimestamp(635912639960000000)

https://community.powerbi.com/t5/Issues/Support-DateTime-Populated-Through-Azure-Cosmos-Table-SDK/id...