Power BI con fechas en listas de SharePoint: problemas de UTC y zona horaria

[Syndicate_Admin]

Hola comunidad,

He leído bastantes publicaciones sobre desafíos con fechas, UTC y zonas horarias que usan Power BI con listas de SharePoint. Y todavía estoy luchando para entenderlo, para ser honesto.

Actualmente estoy cambiando un informe del conector de Sharepoint 2.0 a la API odata para aprovechar la velocidad, la selección de columnas individuales y los nombres internos y me encuentro con el problema. También debe tenerse en cuenta que este informe se puede ejecutar en varios sitios de Sharepoint diferentes (uno por proyecto), algunos de los cuales se ejecutan fuera de Europa, otros fuera de los EE. UU. Estoy en el Reino Unido si eso hace alguna diferencia.

Ejemplo – Proyecto/Sitio 1 - Europa

Sharepoint	Sharepoint 2.0	una vez API (texto)	API odata convertida a fecha/hora/zona horaria
28/02/2023	28/02/2023 00:00	2023-02-27T23:00:00Z	27/02/2023 23:00:00 +00:00
31/03/2023	31/03/2023 00:00	2023-03-30T22:00:00Z	30/03/2023 22:00:00 +00:00

Mi suposición aquí es que se ha almacenado como UTC, que es -2 horas en verano, -1 horas en invierno. La parte de fecha con la API odata es 1 día anterior a Sharepoint.

Ejemplo – Proyecto/Sitio 2 – US

Sharepoint	Sharepoint 2.0	una vez API (texto)	API odata convertida a fecha/hora/zona horaria
10/10/2022	10/10/2022 00:00	2022-10-10T20:00:00Z	10/10/2022 20:00:00 +00:00
14/11/2022	14/11/2022 00:00	2022-11-14T22:00:00Z	14/11/2022 22:00:00 +00:00

No estoy del todo seguro de esto. Si el registro se ha introducido en los EE.UU., ¿UTC es ahora +8 o +10? La parte de fecha con la API odata es la misma que la de SharePoint.

En última instancia, solo quiero que Power BI muestre la misma fecha que la fecha que aparece en Sharepoint. Realmente no me molesta el elemento tiempo. Simplemente no estoy seguro de si hay una solución para cubrir todos los escenarios.

Por favor, comente si tiene alguna sugerencia. Muchas gracias.

[Syndicate_Admin]

sobre el algoritmo, depende de MS tener esto en cuenta. Intenta crear un problema. Vea si eso ayuda.

[Syndicate_Admin]

Gracias de nuevo por toda su ayuda. Claramente hay muchos elementos que podrían estar involucrados. Sin embargo, lo que es interesante para mí es que Sharepoint (y el conector 2.0 en Power BI), aún logran ajustar todos los tiempos "aleatorios" a las 00:00, que es cómo se ingresaron. Me pregunto si ese algoritmo realmente tendría en cuenta todos esos elementos posibles y sabría ajustar UTC en una fecha / fila en 1 hora, otra en 2 horas, otra en 4 horas.

[Syndicate_Admin]

hay algunos factores que podrían contribuir a las discrepancias en las marcas de tiempo de SharePoint y API que está experimentando.

1. Configuración regional del usuario: SharePoint ajusta las horas en función de la configuración regional del sitio, como ha mencionado. Sin embargo, también es importante tener en cuenta que los usuarios individuales pueden tener su propia configuración regional que anule la configuración del sitio. Si estos usuarios han creado o modificado un elemento, la marca de tiempo podría reflejar su configuración regional, no la del sitio.

2. Horario de verano: El horario de verano (DST) podría estar afectando estas marcas de tiempo. En muchas regiones, el horario de verano hace que el reloj se adelante 1 hora en primavera (a menudo en marzo) y retroceda 1 hora en otoño (a menudo en octubre). Estos cambios podrían explicar por qué el registro de octubre es UTC-8 y el registro de noviembre es UTC-10, que incluye un ajuste de horario de verano de 1 hora más una diferencia inesperada de 1 hora. Pero el ajuste de horario de verano solo debería afectar las horas si la ubicación sigue al horario de verano, lo que puede no aplicarse a una configuración UTC + 1 dependiendo de la región específica.

3. Configuración de la hora del servidor: Los servidores que hospedan el entorno de SharePoint o los conectores pueden tener su propia configuración de hora, lo que podría interferir con las horas que está viendo.

4. Problemas de caché o sincronización: A veces, los datos antiguos pueden almacenarse en caché o puede haber retrasos en la sincronización de datos, lo que provoca inconsistencias en las marcas de tiempo.

¿También es posible que su SharePoint tenga una integración de terceros que esté causando los problemas?

[Syndicate_Admin]

Acabo de tener un ejemplo más que me confunde aún más (si eso es posible). De la discusión/investigación, parece que Sharepoint y el conector 2.0 muestran la fecha/hora basada en UTC ajustado por la configuración regional del sitio de SharePoint, lo que explica los resultados mostrados en los ejemplos anteriores.

También tengo otro sitio, con Configuración regional establecida como UTC + 1, donde obtengo los siguientes resultados esperados:

Sharepoint y conector 2.0

Fecha1 31/03/2023 00:00:00

Fecha2 28/02/2023 00:00:00

Conector 1.0

Fecha1 30/03/2023 22:00:00

Fecha2 27/02/2023 23:00:00

API

Fecha1 2023-03-30T22:00:00Z

Fecha2 2023-02-27T23:00:00Z

Por lo tanto, Date1 se ajusta en 2 horas con UTC + 1 y Summer Time, Date2 se ajusta en 1 hora con UTC + 1 base. Todo bien.

Sin embargo, en un sitio diferente, con la configuración regional también establecida como UTC + 1, obtengo los siguientes resultados:

Sharepoint y conector 2.0

Fecha1 10/10/2022 00:00:00

Fecha2 14/11/2022 00:00:00

Conector 1.0

Fecha1 10/10/2022 20:00:00

Fecha2 14/11/2022 22:00:00

API

Fecha1: 2022-10-10T20:00:00Z

Fecha2 2022-11-14T22:00:00Z

Entonces, el registro de octubre de 00:00:00 es UTC-8, el registro de noviembre de 00:00:00 es UTC-10. Así que esto definitivamente no se ajusta en función de UTC + 1, y también la variación de 2 horas entre los dos es extraña. Si fuera una zona horaria con horario de verano, solo esperaría una variación de 1 hora.

¿Hay algo más que pueda estar afectando este extraño ajuste? Muchas gracias 🙂

[Syndicate_Admin]

Tienes razón.

Encantados de ayudar.

[Syndicate_Admin]

Gracias. Pensaré un poco sobre cómo ajustar la hora en función de la configuración regional de SharePoint. Había visto el documento en 1.0 y parece asumir que las zonas horarias de Sharepoint y Local son las mismas. El "ToLocal" ajusta el UTC en función de la zona horaria local de la PC: si la zona horaria de SharePoint es diferente, no funciona, por supuesto.

[Syndicate_Admin]

Configuración regional de SharePoint. Esta es la razón por la que el conector de SharePoint 2.0 muestra la misma hora que lo que se muestra en SharePoint.

Como ya ha descubierto, con la API de OData, la marca de tiempo se devuelve en UTC. Por lo tanto, se espera el comportamiento que está observando, porque la API no conoce la configuración regional del sitio de SharePoint ni la configuración de zona horaria del equipo local. Por lo tanto, no puede aplicar automáticamente el desplazamiento de zona horaria correcto como lo hace el conector de lista de SharePoint.

Para controlar esto en Power BI cuando se usa la API OData de SharePoint, debe:

1. Comprender la configuración regional del sitio de SharePoint y anotar la zona horaria.
2. Determine su propia configuración de zona horaria local.
3. Calcule la diferencia entre la zona horaria del sitio de SharePoint y la zona horaria local.
4. Agregue o reste esta diferencia de la marca de tiempo en Power BI, dependiendo de si la zona horaria local está por delante o por detrás de la zona horaria de SharePoint.

Aunque la solución no es elegante e implica algunos pasos manuales, es la única solución disponible actualmente, ya que Power BI no controla automáticamente las diferencias de zona horaria cuando se usa la API de OData para conectarse a SharePoint.

En cuanto a 1.0 ver este doc. https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-online-list#connect-to-a-sharepo...

[Syndicate_Admin]

Ejecuté algunas pruebas adicionales y todavía estoy bastante confundido en cuanto a cómo Power BI Desktop 2.0 Connector ajusta la hora UTC.

Probé con una lista de Personal 365 Sharepoint de la siguiente manera:

Lista de SharePoint Fecha/hora introducida 20/07/2023 00:00

Conector de Power BI Desktop 2.0 20/07/2023 00:00

Conector de Power BI Desktop 1.0 20/07/2023 07:00

Power BI Desktop odata/API (texto) 2023-07-20T07:00:00Z

Power BI Desktop odata/API (convertido a fecha/hora/Timeone) 20/07/2023 07:00:00 +00:00

Odata/API de Power BI Desktop (convertido alocal) 20/07/2023 08:00:00 +01:00

Lo primero que no entendí aquí fue el desplazamiento UTC de 7 horas. Sin embargo, después de leer un poco, para Personal 365 parece tomar Configuración regional de OneDrive, que en mi caso (no estoy seguro de por qué) se estableció en "UTC -8 Pacífico". Entonces, dado que la fecha es el horario de verano, se convierte en 7 horas. Por lo tanto, la hora UTC está bien según esa lógica. La documentación de Microsoft sugiere que el conector de Sharepoint 2.0 simplemente está haciendo un "ToLocal" para ajustar, y replicar esto si se usa 1.0 o API, pero como se puede ver en mi ejemplo, esto no funciona. Esto claramente toma la zona horaria local de la PC, en mi caso es "UTC +0", pero nuevamente, ajustado para el horario de verano, el ToLocal es correcto a las 08:00 según el UTC.

Entonces, la gran pregunta aquí es cómo sabe Power BI Desktop 2.0 Connector realizar el ajuste basado en la configuración de zona horaria de SharePoint, en mi ejemplo "UTC -8 Pacific". ¿Y cómo replicar esto en 1.0 o la API odata sin codificación?

[Syndicate_Admin]

Hi, @Em_Jay_Jay

De hecho, este es un problema común cuando se trabaja con fechas de SharePoint, el problema es que SharePoint almacena internamente todas sus fechas y horas en formato UTC. Cuando SharePoint muestra una fecha, se ajusta automáticamente a la zona horaria local del sitio donde se muestra la fecha.

El problema surge al conectar SharePoint a Power BI a través de una API o un conector, ya que Power BI recupera los datos en formato UTC. Esto es lo que está causando las discrepancias que está viendo entre SharePoint y Power BI.

Para resolver este problema, debe agregar un ajuste en Power BI para convertir la hora UTC a la zona horaria local correcta. Puede crear una nueva columna en el conjunto de datos de Power BI que aplique una conversión de zona horaria al campo datetime.

Si desea mostrar solo la fecha y no le preocupa la hora, simplemente puede tomar la parte de fecha del valor DateTime. Pero debe agregar un día a las fechas que se cambiaron al día anterior debido a la conversión UTC.

Código de ejemplo

NewDate =
SI (
HORA ( YourTable[OriginalDateTime] ) >= 22,
YourTable[OriginalDateTime] + 1,
YourTable[OriginalDateTime]
)