Forum Discussion
Support for ISO 8601 Date Time format DateTime.ToString("O") unfortunately missing
ericleigh007,
After I enter the key, I am able to directly change the data type of the columns to Date/Time. I am using the latest version of Power BI Desktop(2.52.4921.682 ). In your scenario, please first convert the data type of these columns to Date/Time in Query Editor as described in my first reply, click "Close & Apply" , then you will be able to create calculate column in Data View as shown in the following screenshot.
Regards,
LydiaSo thanks, Lydia,
This is not really a solution. This is a workaround using the query editor, and it works very well.
but the DAX level offers "Modeling" change type, which doesn't work.
Obviously, if the query editor can do this, the Modeling tab should also be able to. In fact, if the Modeling logic in the load worked like query editor, it would have automatically recognized the field as date/time, like other services do.
Thsi is not a matter of "can I do it", it is a matter of "does the tool work as expected"? In this case, I can do it, but the workaround should not be necessary. I changed the title to be more clear about that.
Please submit a bug to the team indicating that the Modeling tab at the DAX level should work properly here.
-thanks very much for you help.
-e
ericleigh007,
When I test the above data in November update of Power BI Desktop, I can directly convert the column to Date/Time type or add a custom column using DateTime.FromText function to convert the type. You can review the following screenshots.
Regards,
Lydia
Sorry for the slowness of response. We were at a trade show and I didn't have time to give this my attention.
I have now tried with teh latest PowerBI, but have the same problem.
It is strange. If I try to convert a lot of data (looks like 206 rows of it), the conversion fails, with this:
If I take the exact date string complained about, and shove it into a new table, the date converts fine.
I'm at a loss, but there is defintely something strange about the first case.
The exact data is bdlow. Also, here's a link to the test file on my OneDrive:
https://1drv.ms/u/s!AuVy-U2vn_fU6a9gP1qXdL-5G-udhA
Start Time
| 2017-11-29T19:13:17.5844778 |
| 2017-11-29T15:05:30.4143152 |
| 2017-11-29T15:23:17.1645022 |
| 2017-11-29T15:28:59.2802492 |
| 2017-11-29T15:35:36.4635923 |
| 2017-11-29T15:54:22.1142031 |
| 2017-11-29T16:14:48.2771755 |
| 2017-11-29T17:11:34.9665393 |
| 2017-11-29T17:17:45.1737229 |
| 2017-11-29T17:36:36.1948156 |
| 2017-11-29T17:42:30.3008343 |
| 2017-11-29T17:54:16.4805842 |
| 2017-11-29T17:59:27.6971981 |
| 2017-11-29T18:18:52.2565458 |
| 2017-11-29T18:47:33.5699438 |
| 2017-11-29T18:53:28.165744 |
| 2017-11-29T20:13:50.8470572 |
| 2017-11-29T20:19:53.1943836 |
| 2017-11-29T20:42:04.9774729 |
| 2017-11-29T21:13:25.1789528 |
| 2017-11-29T21:19:00.4169616 |
| 2017-11-29T21:25:07.3121882 |
| 2017-11-29T21:31:12.5618113 |
| 2017-11-29T21:59:11.7465408 |
| 2017-11-29T22:13:46.7055781 |
| 2017-11-29T22:49:56.2880421 |
| 2017-11-30T13:39:00.5256788 |
| 2017-11-30T13:41:37.3744215 |
| 2017-11-30T13:42:55.3967131 |
| 2017-11-30T13:43:27.5999988 |
| 2017-11-30T14:44:53.0360227 |
| 2017-11-30T14:55:33.1530569 |
| 2017-11-30T15:01:26.6868796 |
| 2017-11-30T15:07:19.9452194 |
| 2017-11-30T15:18:31.5656606 |
| 2017-11-30T15:25:00.0943468 |
| 2017-11-30T15:30:31.7602494 |
| 2017-11-30T15:47:59.2531046 |
| 2017-11-30T15:49:47.826384 |
| 2017-11-30T16:02:51.6626425 |
| 2017-11-30T16:08:53.6439803 |
| 2017-11-30T16:15:13.9378189 |
| 2017-11-30T16:21:47.1068864 |
| 2017-11-30T16:41:41.1444211 |
| 2017-11-30T16:59:39.1646422 |
| 2017-11-30T17:05:56.7599769 |
| 2017-11-30T17:54:02.1010262 |
| 2017-11-30T18:00:39.5858062 |
| 2017-11-30T18:07:08.1324338 |
| 2017-11-30T18:39:53.1569588 |
| 2017-11-30T18:44:52.9608852 |
| 2017-11-30T18:45:46.866952 |
| 2017-11-30T18:53:03.0048297 |
| 2017-11-30T18:59:44.7606842 |
| 2017-11-30T19:15:02.0727926 |
| 2017-11-30T19:22:34.1648285 |
| 2017-11-30T19:24:28.985896 |
| 2017-11-29T15:42:00.6904838 |
| 2017-11-29T16:00:54.7599749 |
| 2017-11-29T16:59:38.5223341 |
| 2017-11-29T17:28:53.4216351 |
| 2017-11-29T18:12:31.3571482 |
| 2017-11-29T18:25:02.2062666 |
| 2017-11-29T18:35:58.3213477 |
| 2017-11-29T18:42:18.9778708 |
| 2017-11-29T18:59:02.4926557 |
| 2017-11-30T17:11:47.3543802 |
| 2017-11-30T17:29:49.4295174 |
| 2017-11-30T17:35:41.9345648 |
| 2017-11-30T17:47:55.8763952 |
| 2017-11-30T18:21:03.2397856 |
| 2017-11-29T15:15:56.2421083 |
| 2017-11-30T16:47:44.4943222 |
| 2017-11-30T17:41:56.6191159 |
| 2017-11-29T14:58:32.700686 |
| 2017-11-29T15:11:16.9341267 |
| 2017-11-29T15:15:56.2511325 |
| 2017-11-29T15:16:50.2985281 |
| 2017-11-29T15:23:17.1735264 |
| 2017-11-29T16:59:38.5313583 |
| 2017-11-29T17:42:30.3098582 |
| 2017-11-29T17:54:16.4906111 |
| 2017-11-29T17:59:27.706222 |
| 2017-11-29T18:18:52.2655696 |
| 2017-11-29T18:47:33.578968 |
| 2017-11-29T18:59:02.5036851 |
| 2017-11-29T19:13:17.5935019 |
| 2017-11-29T20:13:50.8575851 |
| 2017-11-29T20:24:49.6123479 |
| 2017-11-29T20:36:41.7697424 |
| 2017-11-29T21:25:07.3212124 |
| 2017-11-29T21:31:12.5708354 |
| 2017-11-29T21:59:11.7565677 |
| 2017-11-29T22:13:46.7146022 |
| 2017-11-29T22:49:56.2990711 |
| 2017-11-30T13:39:00.5347029 |
| 2017-11-30T13:41:37.3834459 |
| 2017-11-30T13:43:27.6090227 |
| 2017-11-30T14:43:12.9557352 |
| 2017-11-30T14:44:53.0450468 |
| 2017-11-30T14:55:33.1620802 |
| 2017-11-30T15:25:00.1043739 |
| 2017-11-30T15:30:31.7712789 |
| 2017-11-30T16:28:22.4913902 |
| 2017-11-30T16:47:44.5033463 |
| 2017-11-30T16:59:39.1736661 |
| 2017-11-30T17:35:41.9440899 |
| 2017-11-30T17:41:56.6286416 |
| 2017-11-30T18:07:08.1439643 |
| 2017-11-30T18:21:03.2488097 |
| 2017-11-30T18:33:37.5964368 |
| 2017-11-30T18:44:52.9699094 |
| 2017-11-30T18:59:44.7697086 |
| 2017-11-30T19:15:02.0833205 |
| 2017-11-30T19:24:28.9949201 |
| 2017-11-30T19:31:07.2832298 |
| 2017-11-29T15:35:36.4726165 |
| 2017-11-29T15:42:00.6995082 |
| 2017-11-29T17:36:36.2053435 |
| 2017-11-29T21:13:25.1884778 |
| 2017-11-30T13:42:55.4057367 |
| 2017-11-30T15:49:47.8354081 |
| 2017-11-30T16:21:47.1159103 |
| 2017-11-30T16:34:21.4155912 |
| 2017-11-30T17:17:51.4916808 |
| 2017-11-30T18:39:53.165983 |
| 2017-11-30T19:22:34.1738524 |
| 2017-11-29T15:05:30.4233396 |
| 2017-11-29T15:28:59.2892733 |
| 2017-11-29T15:47:40.7672339 |
| 2017-11-29T15:54:22.1232272 |
| 2017-11-29T16:00:54.7689991 |
| 2017-11-29T16:14:48.2867011 |
| 2017-11-29T17:05:06.9807568 |
| 2017-11-29T17:11:34.9765659 |
| 2017-11-29T17:17:45.182747 |
| 2017-11-29T17:23:22.0526163 |
| 2017-11-29T17:28:53.4321633 |
| 2017-11-29T17:48:14.7686558 |
| 2017-11-29T18:06:27.9680275 |
| 2017-11-29T18:12:31.3666736 |
| 2017-11-29T18:25:02.2162935 |
| 2017-11-29T18:30:13.1733028 |
| 2017-11-29T18:35:58.3303718 |
| 2017-11-29T18:42:18.9863934 |
| 2017-11-29T18:53:28.1757704 |
| 2017-11-29T20:19:53.203909 |
| 2017-11-29T20:30:58.6717363 |
| 2017-11-29T20:42:04.9874998 |
| 2017-11-29T21:19:00.4264874 |
| 2017-11-29T21:40:22.546689 |
| 2017-11-29T22:04:54.0834199 |
| 2017-11-29T22:19:13.4369335 |
| 2017-11-30T15:01:26.696405 |
| 2017-11-30T15:07:19.9567504 |
| 2017-11-30T15:12:29.5257417 |
| 2017-11-30T15:18:31.5746848 |
| 2017-11-30T15:36:29.5099445 |
| 2017-11-30T15:47:59.2621287 |
| 2017-11-30T15:56:06.6558407 |
| 2017-11-30T16:02:51.6726691 |
| 2017-11-30T16:08:53.6530044 |
| 2017-11-30T16:15:13.9478455 |
| 2017-11-30T16:41:41.1534452 |
| 2017-11-30T16:53:28.9045223 |
| 2017-11-30T17:05:56.769001 |
| 2017-11-30T17:11:47.363404 |
| 2017-11-30T17:23:51.3242811 |
| 2017-11-30T17:29:49.4385413 |
| 2017-11-30T17:47:55.8854194 |
| 2017-11-30T17:54:02.1100499 |
| 2017-11-30T18:00:39.5948303 |
| 2017-11-30T18:14:20.2251263 |
| 2017-11-30T18:27:20.1912185 |
| 2017-11-30T18:45:46.8759761 |
| 2017-11-30T18:53:03.0143549 |
| 2017-11-29T17:23:22.0430901 |
| 2017-11-29T17:48:14.7596319 |
| 2017-11-29T18:30:13.1642787 |
| 2017-11-29T20:24:49.6028223 |
| 2017-11-29T22:04:54.0738945 |
| 2017-11-30T16:28:22.482366 |
| 2017-11-30T17:17:51.4826569 |
| 2017-11-30T18:33:37.5874126 |
| 2017-11-29T14:58:32.6911604 |
| 2017-11-29T15:11:16.9251028 |
| 2017-11-29T15:16:50.2890027 |
| 2017-11-29T15:47:40.7577085 |
| 2017-11-29T17:05:06.9712314 |
| 2017-11-29T18:06:27.9585023 |
| 2017-11-29T20:30:58.6622109 |
| 2017-11-29T20:36:41.7607181 |
| 2017-11-29T21:40:22.5376649 |
| 2017-11-29T22:19:13.4274078 |
| 2017-11-30T14:43:12.9467111 |
| 2017-11-30T15:12:29.5162163 |
| 2017-11-30T15:36:29.5004192 |
| 2017-11-30T15:56:06.6468163 |
| 2017-11-30T16:34:21.4065671 |
| 2017-11-30T16:53:28.8949971 |
| 2017-11-30T17:23:51.3152568 |
| 2017-11-30T18:14:20.2145986 |
| 2017-11-30T18:27:20.1816931 |
| 2017-11-30T19:31:07.2742059 |
| 2017-11-29T16:10:10.4315138 |
| 2017-11-29T16:10:10.4320153 |
ericleigh007,
It requires to enter credential when I open query editor of your PBIX file. How about you add a custom column as my previous reply?And as your post, when I copy the above date values to new table and import them to Power BI, they are automatically changed to date/time data type.
Regards,
LydiaSent you a direct message containing the CosmosDB read-only key.
-e
ericleigh007,
After I enter the key, I am able to directly change the data type of the columns to Date/Time. I am using the latest version of Power BI Desktop(2.52.4921.682 ). In your scenario, please first convert the data type of these columns to Date/Time in Query Editor as described in my first reply, click "Close & Apply" , then you will be able to create calculate column in Data View as shown in the following screenshot.
Regards,
Lydia