title: Power BI - Desktop DateTime From Azure Cosmos DB
body: <p>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 serialized string in ISO_8601 format (<a href="https://docs.microsoft.com/en-us/azure/cosmos-db/working-with-dates" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/azure/cosmos-db/working-with-dates</a>). When I view my records in the azure portal or the azure storage explorer app, it appears like that is the case.</p>
  
  <p><a href="https://i.stack.imgur.com/qaQFA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qaQFA.png" alt="enter image description here"></a></p>
  
  <p>You can see my StartTime and EndTime properties as well as the TimeStamp are in the format "2019-07-15T16:24:51.392Z"</p>
  
  <p>When I import the data to Power BI Desktop, the value comes out as some bizarre string/number. e.g. </p>
  
  <p>00636988010188048801</p>
  
  <p>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.</p>
  
  <p>The advanced editor looks like the following:</p>
  
  <pre><code>let
      Source = DocumentDB.Contents("https://myurl.documents.azure.com:443/"),
      TablesDB = Source{[id="TablesDB"]}[Collections],
      TablesDB_InstanceData = TablesDB{[db_id="TablesDB",id="InstanceData"]}[Documents],
      #"Expanded Document" = Table.ExpandRecordColumn(TablesDB_InstanceData, "Document", {"id", "RunId", "InstanceId", "StartTime", "EndTime", "MessagesProcessed", "IsCompareInstance", "_ts"}, {"id", "RunId", "InstanceId", "StartTime", "EndTime", "MessagesProcessed", "IsCompareInstance", "_ts"}),
      #"Expanded RunId" = Table.ExpandRecordColumn(#"Expanded Document", "RunId", {"$v"}, {"$v"}),
      #"Expanded InstanceId" = Table.ExpandRecordColumn(#"Expanded RunId", "InstanceId", {"$v"}, {"$v.1"}),
      #"Expanded StartTime" = Table.ExpandRecordColumn(#"Expanded InstanceId", "StartTime", {"$v"}, {"$v.2"}),
      #"Expanded EndTime" = Table.ExpandRecordColumn(#"Expanded StartTime", "EndTime", {"$v"}, {"$v.3"}),
      #"Expanded MessagesProcessed" = Table.ExpandRecordColumn(#"Expanded EndTime", "MessagesProcessed", {"$v"}, {"$v.4"}),
      #"Expanded IsCompareInstance" = Table.ExpandRecordColumn(#"Expanded MessagesProcessed", "IsCompareInstance", {"$v"}, {"$v.5"})
  in
      #"Expanded IsCompareInstance"
  </code></pre>