Date Fields in Power BI

[AW_Grove]

Hi all,

We are bringing GIS tables into Power BI through JSON queries. The date fields, both ESRI generated (such as created date, edit date) and created by us (Install Date, Lining Date) are all coming through as strings. I've tried several dax statements to convert them, but not luck yet. Does anyone who works in the space regularly have any ideas? See attachments for examples of what is coming through. The fields are formatted as dates in the ESRI feature service. 

[RodPBI]

Typically all you need to do is change the data type to Date in Power Query, but the numbers you're using don't translate to date values.  The value for the date 1/1/1996 should be 35065.

[AW_Grove]

@RodPBI  - do you typically use a JSON query when importing data to PowerBI? Maybe that is where the date issue is stemming from.

[RodPBI]

Nope, I'm not very familar with JSON.  I simply looked at the results of your query and took note that the numbers in your date column don't align with what I'm used to seeing for date values.

[AW_Grove]

Thank you for your help. I think it must be related to the JSON query since even the ESRI generated dates (all the same here since I overwrote the service) are wonky. 

[AW_Grove · Accepted solution]

If anyone else has this issue I found a solution. JSON brings the data over in UNIX time. This post has the details on how to convert it:

https://community.esri.com/t5/arcgis-pro-questions/how-do-you-convert-epoch-dates-in-excel-power-bi/...