Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am using Power BI Desktop (Power Query Editor) to import a REST API data source but its converting the date formats to an unintelligible format that I cannot seem to fix (I have even changed the Type to Any and that doesn't change anything).
There are several date columns, for example: reportedDate, beginDate, etc and all have same problem whether or not they are date or date/time
The API link I'm using in the import is here: https://services.arcgis.com/afSMGVsC7QlRK1kZ/arcgis/rest/services/Police_Incidents_2023/FeatureServe...
General Information about the data source: https://opendata.minneapolismn.gov/datasets/cityoflakes::police-incidents-2023/about
This is what the data looks like at the source sample level (dates appear fine):
I have previously brought in the 2010 - 2023 datasets of the same name for years, but I suspect something has changed with the 2023 data.
This is what Power Query is converting my dates to:
All of the following Power Query steps happened automatically from the Web connector type:
How can I solve this issue? I've never seen this happen before. I've seen that format occur in Excel, but not sure how to prevent it in Power Query. I'm also not sure if its a source format issue or an issue I can fix on my end, but from the source sample data above it appears fine.
UPDATE: I was able to convert the values into text and it gives me a string like below, but how can I convert this into a valid date or date/time format?
Thank you in advance for your help!
Solved! Go to Solution.
That looks like epoch time (seconds since 1/1/1970), if you take away a few of the trailing 0s.
Epoch Converter - Unix Timestamp Converter
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
That looks like epoch time (seconds since 1/1/1970), if you take away a few of the trailing 0s.
Epoch Converter - Unix Timestamp Converter
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you! That pointed me in the right direction and lead me to this video from Curbal https://www.youtube.com/watch?v=IEHq-rUcf-g which helped me create the below code (had to convert the #duration calculation from milliseconds which is what the original epoch time column is in, so that is why there is a divide by 1000 added).
Below presumes the original column has been converted to Text format (hence my step of "Changed epoch dates to text" prior to this TransformColumns step):
= Table.TransformColumns(#"Changed epoch dates to text", {{"features.properties.reportedDate", each #datetime(1970,1,1,0,0,0) +#duration(0,-6,0,Number.From(_)/1000), type datetime}})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |