Support for ISO 8601 Date Time format DateTime.ToString("O") unfortunately missing

[ericleigh007]

I found it surprising that the ISO-8601 format is not recognized as DateTime by the CosmosDB connector. To be clear, these types of data cannot be converted to DateTime.

2017-11-21T21:04:22.8041391Z
2017-11-21T21:04:22.8041391Z
2017-11-21T21:04:33.3946362Z
2017-11-21T21:04:33.3946362Z
2017-11-21T21:04:43.873456Z
2017-11-21T21:04:43.873456Z
2017-11-21T21:04:54.3789883Z
2017-11-21T21:04:54.3789883Z

"We can't automatically convert the column to a Date/Time type", which forces me to do text manipulation of the field until powerBi recognizes it. I'm reporting this as a bug, because in looking at IOT data this probably the most common form known. -thanks -e

[v-yuezhe-msft · Accepted solution]

@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

[v-yuezhe-msft]

@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

[ericleigh007]

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

[v-yuezhe-msft]

@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,
Lydia

[ericleigh007]

Sent you a direct message containing the CosmosDB read-only key.

-e

[v-yuezhe-msft · Accepted solution]

@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

[ericleigh007 · Accepted solution]

So 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]

I will have to loo at my data in detail, for MY DATA cannot be converted to the DateTime dta type.  There could be a l8ne that is mlformed somehow.  As usual, a more descriptive 3rror message wo7ld have helped.

Let me g3t soe time for ivetigsationand I will share my fringe.

Thaks for such a great product.