Ú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

Queridos todos,

Tengo una tabla de fechas (con fechas, horas, 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 unión en la línea de

DONDE tabla de fechas[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 contra los datos en una tabla de fecha y hora que mide 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 has dado me servirá 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 invertir las cosas.

En cambio, 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, solo 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.

miguelparber

En respuesta a ImkeF

@ImkeF Gracias por tu código:

Estoy tratando de hacer algo similar pero tengo problemas para ajustar su código.

en mi caso tengo uno Tasa de audición 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 miguelparber

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

miguelparber

En respuesta a ImkeF

¡Estupendo! ¡Funcionó!

¡Gracias @ImkeF!

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

(HR) =>

(OT) =>

ImkeF

En respuesta a miguelparber

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! Probaré eso y te dejaré saber cómo me va. Ausente durante la próxima semana, ¡lo intentaré dentro de una semana!

robertsbd

En respuesta a robertsbd

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

ImkeF

En respuesta a robertsbd

OK, esa es una tabla de fechas bastante larga. Entonces, ¿realmente lo necesitas en el nivel de minutos? ¿Sus eventos son más bien cortos? ¿Qué tan larga será 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 los intervalos de tiempo necesarios:

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 DAX para «Eventos en progreso» como aquí: http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

Aunque podría cambiar el arrastre de rendimiento de carga a 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 está gustando 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 universitario. 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 Power Query, por lo que no hay problema.

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

Estoy comparando los datos de horarios con los datos que se muestrean en intervalos de 15 minutos en un sistema diferente que mide los datos de ocupación de todas las habitaciones. 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 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 que las habitaciones están ocupadas en este nivel dado. Esta es una buena característica para que el usuario vea qué y a qué hora están 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 salas.

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

Es posible que tenga que usar la fuerza bruta durante la noche revisando la tabla de horarios e insertando nuevas filas para cada evento para cubrir todas las veces que ocurren 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 has dado me servirá 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 invertir las cosas.

En cambio, 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 *