La porción de tiempo de MongoDB ODBC del campo ISODate no se devuelve

–ACTUALIZAR–

He avanzado un poco en averiguar qué está causando el comportamiento que estoy viendo. Utilicé el software de prueba ODBC para probar la conexión a mi base de datos Mongo y los datos que se devuelven. La fecha completa, incluida la hora y la zona horaria, está presente, así que sé que la fecha está llegando intacta a ODBC, por lo que parece que el problema está en la forma en que PowerBI consulta los datos. Mirando esto más a fondo, abrí el editor avanzado dentro del editor de consultas y reemplacé esto:

let
    Source = Odbc.DataSource("dsn=MongoDSN", [HierarchicalNavigation=true]),
    test_Database = Source{[Name="testDb",Kind="Database"]}[Data],
    test_Table = test_Database{[Name="testTable",Kind="Table"]}[Data]
in
    test_Table

con este:

let
    Source = Odbc.DataSource("dsn=MongoDSN", "select * from testTable")
in
    Source

lo que me da la fecha completa, incluida la hora y la zona horaria, por lo que es casi seguro que el problema esté dentro de la consulta. Tuve la habilidad de encontrar las consultas dentro de los registros de ODBC:

-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:42

-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:43

1534929523:SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM ( SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA LIKE 'testDb' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) ) TABLES WHERE TABLE_NAME LIKE 'testTable';
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:43

-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:44

1534929524:SHOW KEYS FROM `testDb`.`testTable`;
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:44

1534929524:SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME,A.COLUMN_NAME AS FKCOLUMN_NAME,A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,'PRIMARY' AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE D ON (D.TABLE_SCHEMA=A.REFERENCED_TABLE_SCHEMA AND D.TABLE_NAME=A.REFERENCED_TABLE_NAME AND D.COLUMN_NAME=A.REFERENCED_COLUMN_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND R.TABLE_NAME = A.TABLE_NAME AND R.CONSTRAINT_SCHEMA = A.TABLE_SCHEMA) WHERE D.CONSTRAINT_NAME IS NOT NULL AND A.TABLE_SCHEMA = 'testDb' AND A.TABLE_NAME = 'testTable' ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ, PKTABLE_NAME;
1534929524:Using direct execution;
1534929524:query has been executed;
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:45

1534929525:SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME,A.COLUMN_NAME AS FKCOLUMN_NAME,A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,'PRIMARY' AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE D ON (D.TABLE_SCHEMA=A.REFERENCED_TABLE_SCHEMA AND D.TABLE_NAME=A.REFERENCED_TABLE_NAME AND D.COLUMN_NAME=A.REFERENCED_COLUMN_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND R.TABLE_NAME = A.TABLE_NAME AND R.CONSTRAINT_SCHEMA = A.TABLE_SCHEMA) WHERE D.CONSTRAINT_NAME IS NOT NULL AND A.REFERENCED_TABLE_SCHEMA = 'testDb' AND A.REFERENCED_TABLE_NAME = 'testTable' ;
1534929525:Using direct execution;
1534929525:query has been executed;
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:45

1534929525:select `_id`,

    `date`

from `testDb`.`testTable`

order by `_id`;
1534929525:Using direct execution;
1534929525:query has been executed;

¿Alguien puede detectar un problema con las consultas? No veo nada obvio que pudiera estar causando el problema.

Puedo usar «select * from testTable» como una solución por ahora, pero esto me parece un error.

Hola @rob_nolan

Puede intentar verificar el formato del valor en su fuente de datos. Si no es su caso, puede enviar un ticket de soporte.

https://powerbi.microsoft.com/en-us/support/

Saludos,

Cherie