Únase con mayor que Y menor que para unir la tabla de fecha y hora contra eventos con fechas de inicio y finalización

Un usuario Pregunto ✅

Robertsbd

Estimados,

Tengo una tabla de fechas (con fechas, años, meses, días, horas, minutos). Luego tengo una tabla de eventos que define las fechas de inicio y finalización de los eventos. Necesito poder decir en qué filas de la tabla de fechas están ocurriendo los eventos. Para hacer esto en SQL u otros entornos de BI, haría una combinación en la línea de

DONDE datetable[datetime] > = eventos[start date] Y tabla de fechas[datetime] <= eventos[end date]

Esto no parece ser una opción en Power query. Es una tabla de 1.8 millones de filas de eventos, por lo que necesito poder hacer este cálculo de manera bastante eficiente.

La razón por la que necesito hacer esto es mapear los datos de reserva de habitaciones con los datos en una tabla de fecha y hora que mida la ocupación en la habitación.

¿Cómo debo abordar este problema en Power BI?

Atentamente,

Ben

ImkeF

En respuesta a Robertsbd

Eso suena genial y debería ser factible.

Por lo tanto, solo necesita crear intervalos de 15 minutos, lo que acelerará significativamente la carga y reducirá la longitud de la tabla. Simplemente reemplace los siguientes pasos con este código:

    DurationMinutes = Table.AddColumn(#"Removed Columns1", "Minutes", each if [Custom]=[start date] then {Number.RoundDown(Duration.Minutes([Duration])/15,0)..3} else if [Custom]=[end date] then {0..Number.RoundDown(Duration.Minutes([Duration])/15,0)} else {0..3}),
    #"Expanded Minutes" = Table.ExpandListColumn(DurationMinutes, "Minutes"),
    Minutes = Table.AddColumn(#"Expanded Minutes", "DateTime", each [DayHour]+#duration(0,0,[Minutes]*15,0)),

Robertsbd

En respuesta a ImkeF

Hola,

¡Gracias por tu ayuda! Ese código que me ha dado será útil para algunos otros problemas. Después de jugar con esto, descubrí que estaba pensando en las cosas de manera incorrecta. En cambio, lo que hice fue revertir las cosas.

En su lugar, lo calculé en DAX en una columna calculada en la tabla de horarios. Básicamente, busqué el valor máximo de ocupación de la habitación para un evento determinado.

Max Occupancy (APC) = 
CALCULATE(
     MAX('APC Data'[People Inside]),
          FILTER( 
               FILTER('APC Data', 'APC Data'[RoomName] = 'CELCAT Timetabling'[RoomName]),
                'APC Data'[DateTime] >= 'CELCAT Timetabling'[DateTime Start]
&& 'APC Data'[DateTime] <= 'CELCAT Timetabling'[DateTime End] ) )

ImkeF

En realidad, no es muy diferente en M, simplemente agregue una columna a su tabla de eventos y expanda el resultado:

DateAllocation= Table.AddColumn(
                 Events, 
                "Custom", 
                 (FilterTable) => Table.SelectRows(
                                    Table.Buffer(Dates), 
                                    (ValuesTable)=> ValuesTable[datetime]>= FilterTable[start date] 
                                    and ValuesTable[datetime]<=FilterTable[end date]
                                )
                             )

El Table.Buffer es esencial para la velocidad.

michaelshparber

En respuesta a ImkeF

@ImkeF Gracias por tu código:

Estoy tratando de hacer algo similar pero estoy luchando por ajustar su código.

En mi caso tengo uno HearRate tabla con HR_DateTime columna

y otro Entrenamientos tabla con WO_StartDateTime y Columnas WO_EndDateTime

Me gustaría traer un WorkoutType a mi tabla de recursos humanos cuando HR_DateTime> = WO_StartDateTime y HR_DateTime <= WO_EndDateTime.

Siento que se supone que su código debe ayudar, pero está agregando la columna expandible a la tabla de Eventos (Entrenamientos en mi caso), pero necesito llevarlo a mi tabla de recursos humanos …

Por favor ayuda

Gracias

Miguel

DateAllocation= Table.AddColumn(
                 Events, 
                "Custom", 
                 (FilterTable) => Table.SelectRows(
                                    Table.Buffer(Dates), 
                                    (ValuesTable)=> ValuesTable[datetime]>= FilterTable[start date] 
                                    and ValuesTable[datetime]<=FilterTable[end date]
                                )
                             )

«

ImkeF

En respuesta a michaelshparber

Hola @michaelshparber,

si mi comprensión es correcta, debería funcionar así:

DateAllocation= Table.AddColumn(
                 Events, 
                "Custom", 
                 (HR) => Table.SelectRows(
                                    Table.Buffer(YourWorksoutsTable), 
                                    (WO)=> HR[HR_DateTime] >= WO[WO_StartDateTime] 
                                    and HR[HR_DateTime] <= WO[WO_EndDateTime]
                                )
                             )

michaelshparber

En respuesta a ImkeF

¡Excelente! ¡Funcionó!

¡Gracias @ImkeF!

¿Cuál es la sintaxis que estás usando aquí?

(HR) =>

(WO) =>

ImkeF

En respuesta a michaelshparber

Estos son puntos de partida para las definiciones de funciones: https://docs.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions

Robertsbd

En respuesta a ImkeF

¡Gracias! Lo intentaré y les haré saber cómo me llevo. ¡Fuera para la próxima semana, así que tendré una oportunidad en una semana!

Robertsbd

En respuesta a Robertsbd

Estoy ejecutando esto ahora, sin embargo, iba muy lento y tenía alrededor de 60 filas y luego se quedó sin memoria. ¿Se exceptuaría esto? La tabla de eventos tiene 1,8 millones de filas y la tabla de fechas 47.000 filas.

ImkeF

En respuesta a Robertsbd

De acuerdo, esa es una tabla de fechas bastante larga. ¿Entonces realmente lo necesitas a nivel de minutos? ¿Son sus eventos bastante cortos / cuánto tiempo durará la tabla resultante? ¿Estás ejecutando 64 bits?

De todos modos, este es un enfoque diferente: sin fusión, solo cálculo paso a paso de las inversiones de tiempo necesarias:

let
    Source = SourceEvents,
    Duration = Table.AddColumn(Source, "Duration", each [end date]-[start date]),
    DurationDays = Table.AddColumn(Duration, "Days", each {0..Duration.Days([Duration])}),
    #"Expanded Days" = Table.ExpandListColumn(DurationDays, "Days"),
    Days = Table.AddColumn(#"Expanded Days", "Custom", each [start date]+#duration([Days],0,0,0)),
    #"Removed Columns" = Table.RemoveColumns(Days,{"Days"}),
    DurationHours = Table.AddColumn(#"Removed Columns", "Hours", each if [Custom]=[start date] then {Duration.Hours([Duration])..23} else if [Custom]=[end date] then {0..Duration.Hours([Duration])} else {0..23}),
    #"Expanded Hours" = Table.ExpandListColumn(DurationHours, "Hours"),
    Hours = Table.AddColumn(#"Expanded Hours", "DayHour", each [Custom]+#duration(0,[Hours],0,0)),
    #"Removed Columns1" = Table.RemoveColumns(Hours,{"Hours"}),
    DurationMinutes = Table.AddColumn(#"Removed Columns1", "Minutes", each if [Custom]=[start date] then {Duration.Minutes([Duration])..59} else if [Custom]=[end date] then {0..Duration.Minutes([Duration])} else {0..59}),
    #"Expanded Minutes" = Table.ExpandListColumn(DurationMinutes, "Minutes"),
    Minutes = Table.AddColumn(#"Expanded Minutes", "DateTime", each [DayHour]+#duration(0,0,[Minutes],0)),
    #"Removed Columns2" = Table.RemoveColumns(Minutes,{"Duration", "Custom", "DayHour", "Minutes"})
in
    #"Removed Columns2"

De lo contrario, eche un vistazo a los enfoques de DAX para «Eventos en curso» como aquí: http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

Aunque puede cambiar el rendimiento-arrastre de la carga a la consulta …

Robertsbd

En respuesta a ImkeF

Muchas gracias por su ayuda. Ha sido útil para determinar qué hacer. También es un buen problema aprender más sobre M, que me gusta cuanto más lo entiendo.

Lo que estoy haciendo es realizar un análisis de los datos de horarios durante un período de dos años para un gran sistema de horarios universitarios. Es un análisis para examinar la eficiencia del uso del espacio, etc.

Los datos de horarios están en forma de evento, nombre de la sala, hora de inicio, hora de finalización …

La mayoría de los análisis son bastante sencillos sin necesidad de realizar un gran procesamiento en la consulta de potencia, por lo que no hay problema.

El único análisis que constituye el problema con el que se relaciona esta consulta es:

Estoy comparando los datos de horarios con los datos muestreados en intervalos de 15 minutos en un sistema diferente que mide los datos de ocupación de todas las habitaciones. Les presento un histograma de ocupación de habitaciones. es decir, con qué frecuencia las habitaciones tienen una ocupación del 85% o más. Esto no es un problema y es bastante agradable.

Entonces puedo hacer clic en la parte del histograma que filtrará una tabla de los nombres de las habitaciones y las horas en las que las habitaciones están ocupadas en este nivel determinado. Esta es una buena característica para que el usuario vea qué y a qué hora está causando los eventos de alta ocupación. Me gustaría que también pudieran ver quién es el departamento de reservas, qué tipo de evento fue, todos los datos de la tabla de ocupación de la habitación.

Lo que quiero hacer es obtener el evento (es decir, qué departamento lo reservó, qué tipo de evento fue) de la tabla de horarios. Sin embargo, no puedo simplemente tener una relación entre los tiempos en los datos de ocupación y los eventos de programación de horarios, ya que la tabla de horarios no especifica para una habitación determinada en un momento dado lo que está sucediendo. Entonces creo que necesito crear una nueva tabla que especifique esto, luego puedo simplemente relacionar la tabla de horarios (información de eventos) con la tabla de ocupación de la habitación.

Es posible que tenga que forzar esto de la noche a la mañana revisando la tabla de horarios e insertando nuevas filas para cada evento para cubrir todas las veces que están ocurriendo y luego hacer un relleno.

ImkeF

En respuesta a Robertsbd

Eso suena genial y debería ser factible.

Por lo tanto, solo necesita crear intervalos de 15 minutos, lo que acelerará significativamente la carga y reducirá la longitud de la tabla. Simplemente reemplace los siguientes pasos con este código:

    DurationMinutes = Table.AddColumn(#"Removed Columns1", "Minutes", each if [Custom]=[start date] then {Number.RoundDown(Duration.Minutes([Duration])/15,0)..3} else if [Custom]=[end date] then {0..Number.RoundDown(Duration.Minutes([Duration])/15,0)} else {0..3}),
    #"Expanded Minutes" = Table.ExpandListColumn(DurationMinutes, "Minutes"),
    Minutes = Table.AddColumn(#"Expanded Minutes", "DateTime", each [DayHour]+#duration(0,0,[Minutes]*15,0)),

Robertsbd

En respuesta a ImkeF

Hola,

¡Gracias por tu ayuda! Ese código que me ha dado será útil para algunos otros problemas. Después de jugar con esto, descubrí que estaba pensando en las cosas de manera incorrecta. En cambio, lo que hice fue revertir las cosas.

En su lugar, lo calculé en DAX en una columna calculada en la tabla de horarios. Básicamente, busqué el valor máximo de ocupación de la habitación para un evento determinado.

Max Occupancy (APC) = 
CALCULATE(
     MAX('APC Data'[People Inside]),
          FILTER( 
               FILTER('APC Data', 'APC Data'[RoomName] = 'CELCAT Timetabling'[RoomName]),
                'APC Data'[DateTime] >= 'CELCAT Timetabling'[DateTime Start]
&& 'APC Data'[DateTime] <= 'CELCAT Timetabling'[DateTime End] ) )

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *