Consulta/Función personalizada para convertir UTC a EST

[Anonymous]

Estoy extrayendo una serie de columnas datetime de varias fuentes, que están en su mayoría en UTC. Estoy usando ToLocal para mostrar estos en EST, que es mi hora local, y es cómo mis usuarios finales necesitan ver los datos.

Sin embargo, esto no funciona en el informe después de publicar en el servicio PowerBI, que parece usar UTC como su hora local.

Si mi origen de datos lee 18:00 UTC, ToLocal no tiene ningún efecto en los datos, todavía se muestra como 18:00 en el servicio PowerBI.

Una solución simple sería simplemente restar X horas de mis horas UTC en PowerQuery. Los datos se "verían" como EST en PowerBI Desktop y "se verían" como EST en el servicio PowerBI (aunque PowerBI crea que es una hora UTC).

Sin embargo, esto no tiene en cuenta el ahorro de luz diurna en absoluto, lo que no es trivial, porque el horario de verano comienza y termina en un día diferente cada año.

Así que me gustaría una función M (o algún otro enfoque creativo) que puede...

• Leer en un DateTime que está en UTC
• Comprueba si DateTime cae en el horario de verano
  • En caso afirmativo, restar 5 horas
  • Si no, reste 4 horas
• Se aplica fácilmente a varias columnas

¿Alguna idea?

[Anonymous]

Estoy extrayendo una serie de columnas datetime de varias fuentes, que están en su mayoría en UTC. Estoy usando ToLocal para mostrar estos en EST, que es mi hora local, y es cómo mis usuarios finales necesitan ver los datos.

Sin embargo, esto no funciona en el informe después de publicar en el servicio PowerBI, que parece usar UTC como su hora local.

Si mi origen de datos lee 18:00 UTC, ToLocal no tiene ningún efecto en los datos, todavía se muestra como 18:00 en el servicio PowerBI.

Una solución simple sería simplemente restar X horas de mis horas UTC en PowerQuery. Los datos se "verían" como EST en PowerBI Desktop y "se verían" como EST en el servicio PowerBI (aunque PowerBI crea que es una hora UTC).

Sin embargo, esto no tiene en cuenta el ahorro de luz diurna en absoluto, lo que no es trivial, porque el horario de verano comienza y termina en un día diferente cada año.

Así que me gustaría una función M (o algún otro enfoque creativo) que puede...

• Leer en un DateTime que está en UTC
• Comprueba si DateTime cae en el horario de verano
  • En caso afirmativo, restar 5 horas
  • Si no, reste 4 horas
• Se aplica fácilmente a varias columnas

¿Alguna idea?

[edhans]

Me alegra que @Shart2019 ayudara

[edhans · Accepted solution]

Para obtener la zona horaria local, utilice la siguiente función, que creé en una consulta en blanco.

= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-7)

Cambie el -7 a su desplazamiento normal no DST.

También llamé a esa consulta varToday

Ahora cree otra consulta como esta:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LczBDcAwCATBXvy2BIcT2anFov82ErL+7WkEe7dhCgvXbL1JdtPZf1GthVz0Ea/1IING1jfCAdHnpB6EkElnvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"dtDSTStart", type date}, {"dtDSTEnd", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (DateTime.Date(varToday) >= [dtDSTStart] and DateTime.Date(varToday) <= [dtDSTEnd])),
    #"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Counted Rows"

Eso devolverá un 1 o 0. 1 si estamos en DST, 0 si no. Llamé a esta consulta varDST Nota que hace referencia a varToday anterior.

Ahora todas las demás funciones deben usar la misma lógica que DateTimeZone.SwitchZone([UTC],-7) pero se agrega varDST, que agregará 1 o 0.

1) En Power Query, seleccione Nuevo origen y, a continuación, Consulta en blanco
2) En la cinta inicio, seleccione el botón "Editor avanzado"
3) Retire todo lo que ve, luego pegue el código M que le he dado en ese cuadro.
4) Pulse Hecho

EDITAR: Esto no funcionará en las pocas horas de la mañana en las fechas del cambio de horario de propiedad. Se supone que todo el día es o no es DST. YOu'd necesita para ampliar en gran medida la mesa para manejar el interruptor 2am-3am.

[Anonymous]

@edhans Esto es útil, pero no estoy seguro de que sea 100% lo que necesito.

Esta consulta se basa en la fecha actual para determinar si estamos en DST o no, ¿no? La solución debe comprobar la fecha que se está convirtiendo.

¿Imaginé que tengo tres años de datos todos en UTC, y necesito convertirlos todos a PENSAMIENTOs EST?

La solución tendrá que ser alguna función personalizada que tome la fecha como entrada, en lugar de confiar en varToday. ¡No estoy lo suficientemente versado en M para escribirlo!

[edhans]

Sólo tiene que cambiar la función para tirar de su campo, contra la hora actual.

= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-7)

becomes

= DateTimeZone.SwitchZone([YourUTCDateTimeZoneField],-7)

En otras palabras, no usaría varToday. Simplemente agregue una columna personalizada que podría tener esta fórmula completa

= DateTimeZone.SwitchZone([YourUTCDateTimeZoneField],-7 + varDST)

¿Tiene sentido? Si no es así, envíe algunos datos a través de enlaces a continuación y una captura de pantalla desde Excel de la salida esperada.

Cómo obtener una buena ayuda rápidamente. Ayúdanos a ayudarte.
Cómo obtener respuesta rápida a su pregunta
Cómo proporcionar datos de ejemplo en el foro de Power BI

[Anonymous]

---

@edhans escribió:

Sólo tiene que cambiar la función para tirar de su campo, contra la hora actual.

= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-7)

becomes

= DateTimeZone.SwitchZone([YourUTCDateTimeZoneField],-7)

Creo que hay alguna desconexión aquí. No puedo (y no debería) hacer referencia a una columna de una diferente en esta consulta.

Tengo muchos campos / columnas que necesitan ser convertidos, no sólo uno.

Por lo tanto, la solución debe tomar una fecha y hora como entrada, determine que la fecha específica está dentro de DST y, a continuación, ajusta esa fecha y hora de forma adecuada. De esta manera puedo aplicar la solución a cualquier número de columnas.

En cualquier caso, sus publicaciones fueron útiles, finalmente escribí una función personalizada!

[Syndicate_Admin]

@RandomUser01 muchas gracias! esto hizo exactamente lo que estaba buscando. Sólo tiene una pregunta, ¿se ajustará automáticamente a DTS?

[Syndicate_Admin]

Sin @Jruiz218 - Usted tiene que hacer los cambios de DST usted mismo. Tengo un artículo aquí que muestra cómo agregar una marca de tiempo de actualización a su informe y se describe cómo se puede ajustar para DST con una tabla que tiene las fechas de inicio / fin de DST durante unos años. Utilice las fechas correctas para su región. Estas fechas son para los EE.UU.

[Syndicate_Admin]

@Jruiz218 sí, esta función se ajusta para DST. No estoy seguro de por qué @edhans está diciendo que no.

Como aparte, me pareció que el rendimiento de esta función era muy pobre - si está convirtiendo muchas fechas, le sugiero que cree una tabla DST en PowerQuery, luego haga referencia a esa tabla en la función --

let
    UTCtoEST = (UTC_DateTime) =>
    let 
        Source = Table.Buffer(Table_DST),
        #"Filtered Rows" = Table.SelectRows(Source, each (DateTime.Date(UTC_DateTime) >= [dtDSTStart] and DateTime.Date(UTC_DateTime) <= [dtDSTEnd])),
        result = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTime.AddZone(UTC_DateTime,0),-5 + Table.RowCount(#"Filtered Rows")))
    in
        result
in
    UTCtoEST

[Syndicate_Admin]

Lo siento @RandomUser01 - usted está haciendo algún ajuste a DST, pero no hay un calendario DST. Consulte este artículo para 2021. Muchos países no tienen DST, y muchos otros tienen calendarios diferentes, y otros tienen múltiples calendarios DST (o la falta de ellos) en diferentes áreas. Arizona en los EE.UU. como un ejemplo no tiene DST.

Es por eso que le dije a @Jruiz218 tendrían que mirar el artículo que provisté y ajustar para DST de acuerdo con la zona horaria según corresponda.

Pero sí, si usted y @Jruiz218 están en el mismo calendario DST, entonces funciona.

[Anonymous]

Gracias por publicar! Me pareció muy útil para resolver un problema similar.