En la segunda nota vimos las grandes ventajas que nos ofrece el uso de Power Query al hacer los cálculos combinando los informes de varios empleados, en forma automática y sin necesidad de recrear las fórmulas con cada nuevo informe.
En ambas notas mostramos el caso sencillo donde no hay que diferenciar las horas por turnos. En este post vamos a recrear el ejemplo del segundo pero agregando la necesidad de calcular cuántas de las horas trabajadas fueron en el turno diurno y cuántas en el turno nocturno.
De hecho vamos a estar recreando en Power Query el modelo que mostré en este post. La lógica aplicada es la misma pero "traducida al idioma" Power Query. Las ventajas, como ya expliqué en las notas anteriores, son:
- la robustez del modelo: no hay fórmulas en celdas que puedan ser alteradas desapercibidamente;
- la facilidad de combinar un gran número de fuentes de datos y crear informes;
- actualización de los datos e informes con sólo apretar un botón.
Para este ejemplo vamos a usar los mismos archivos del segundo post (los informes de los empleados que guardamos en la carpeta Informes).
Al crear este nuevo modelo el primer paso es definir las horas de comienzo y finalización del turno noche. En una hoja de Excel ponemos esta sencilla tabla
Creamos una consulta a la tabla como "sólo conexión"
Podemos ver PASOS APLICADOS que Power Query ha cambiado el tipo de dato de las columnas Comienza y Termina a número decimal (identificado por 1.2 a la izquierda del encabezado de la columna). Ésto sirve a nuestros propósitos así que lo aceptamos.
A partir de esta consulta creamos una nueva con la opción Referencia
Abrimos la nueva conulta y cambiamos el nombre a "noche_comienzo".Con un clic del botón derecho del mouse y apuntando al valor en la fila Comienza y activamos la opción "Rastrear desagrupando datos" (en inglés sencillamente, drill down)
con este resultado
Al crear este nuevo modelo el primer paso es definir las horas de comienzo y finalización del turno noche. En una hoja de Excel ponemos esta sencilla tabla
Podemos ver PASOS APLICADOS que Power Query ha cambiado el tipo de dato de las columnas Comienza y Termina a número decimal (identificado por 1.2 a la izquierda del encabezado de la columna). Ésto sirve a nuestros propósitos así que lo aceptamos.
A partir de esta consulta creamos una nueva con la opción Referencia
Abrimos la nueva conulta y cambiamos el nombre a "noche_comienzo".Con un clic del botón derecho del mouse y apuntando al valor en la fila Comienza y activamos la opción "Rastrear desagrupando datos" (en inglés sencillamente, drill down)
con este resultado
Ahora guardamos esta consulta también como "sólo conexión". Repetimos el proceso para crear una consulta para el valor de "noche_final", esta vez seleccionando el valor de la columna Termina.
Nuestra hoja se ve ahora así
Ahora vamos a aplicar los pasos que aplicamos en la segunda nota para crear una consulta que integre todos los informes (archivos xlsx) guardados en la carpeta Informes. Al final de proceso tenemos esta consulta en la ventana del editor de PQ
Aquí vamos a hacer un pequeño cambio a lo que mostramos en la segunda nota. Vamos a extraer de Entrada y Salida le fecha y dejar sólo las horas. Para hacerlo seleccionamos ambas columnas y aplicamos en Transformar-Columna de Fecha y Hora-Solo hora
ahora tendremos
La próxima transformación que hacemos es transformar las horas de Entrada y Salida al tipo de datos número decimal.
Ahora vamos a aplicar la misma lógica que usamos en la solución con Excel tradicional
# - calculamos las horas trabajadas con una columna personalizada con esta fórmula
# agregamos dos columnas auxiliares "noche-comienzo"
y "noche-final" de la misma manera . Cambiamos el tipo de datos a número decimal.
# creamos una columna personalizada que llamaremos "Mediana - Entrada". Ésta es una columna auxiliar (con el mismo objetivo de la correspondiente columna en el modelo en Excel ya mencionado). Seleccionamos la columna Entrada y las dos columnas que acabamos de crear y creamos una nueva columna con Agregar Columnas-Estadísticas-Mediana
La columna creada recibe, por defecto, el nombre "Median" que vamos a reemplazar con "Mediana-Entrada". Ésto podemos hacerlo agregando un paso o, como muestro en la imagen abajo, reemplazando el valor en la ventana de las fórmulas
Hacemos lo mismo con Salida-noche_comienzo y noche_final para crear la columna "Mediana-Salida".
# creamos una nueva columna auxiliar, "aux1" con esta fórmula condicional
# ahora calculamos las horas trabajadas en el turno noche con esta fórmula:
# todo lo que nos queda por hacer es calcular las horas diurnas que, lógicamente, son la diferencia entre las horas trabajadas y las horas nocturnas
# Finalmente eliminamos todas las columnas auxiliares que creamos y dejamos solamente aquellas que queremos que aparezcan en nuestro informe
Transformamos las columnas de horas al tipo de dato "Hora" y cargamos la consulta a una hoja del cuaderno
Para crear un informe que resuma los datos por empleados empezamos por crear una nueva consulta que cuyo origen es una referencia a la que acabamos de crear. Cambiamos el tipo de datos de las columnas de horas a número decimal y aplicamos "Agrupar por" de esta manera
Cargamos la consulta a una hoja del cuaderno y cambiamos el formato de las columnas de las horas al tipo personalizado [hh]:mm en el menú de formato de celdas de Excel (ver la explicación en la nota anterior).
Nuestro resumen se verá así
Nuestra hoja se ve ahora así
Ahora vamos a aplicar los pasos que aplicamos en la segunda nota para crear una consulta que integre todos los informes (archivos xlsx) guardados en la carpeta Informes. Al final de proceso tenemos esta consulta en la ventana del editor de PQ
Aquí vamos a hacer un pequeño cambio a lo que mostramos en la segunda nota. Vamos a extraer de Entrada y Salida le fecha y dejar sólo las horas. Para hacerlo seleccionamos ambas columnas y aplicamos en Transformar-Columna de Fecha y Hora-Solo hora
ahora tendremos
La próxima transformación que hacemos es transformar las horas de Entrada y Salida al tipo de datos número decimal.
Ahora vamos a aplicar la misma lógica que usamos en la solución con Excel tradicional
# - calculamos las horas trabajadas con una columna personalizada con esta fórmula
=if [Salida]<[Entrada] then1+[Salida]-[Entrada]else[Salida]-[Entrada]
y "noche-final" de la misma manera . Cambiamos el tipo de datos a número decimal.
# creamos una columna personalizada que llamaremos "Mediana - Entrada". Ésta es una columna auxiliar (con el mismo objetivo de la correspondiente columna en el modelo en Excel ya mencionado). Seleccionamos la columna Entrada y las dos columnas que acabamos de crear y creamos una nueva columna con Agregar Columnas-Estadísticas-Mediana
La columna creada recibe, por defecto, el nombre "Median" que vamos a reemplazar con "Mediana-Entrada". Ésto podemos hacerlo agregando un paso o, como muestro en la imagen abajo, reemplazando el valor en la ventana de las fórmulas
Hacemos lo mismo con Salida-noche_comienzo y noche_final para crear la columna "Mediana-Salida".
# creamos una nueva columna auxiliar, "aux1" con esta fórmula condicional
[Horas trabajadas]+[#"Mediana-Entrada"]-[#"Mediana-Salida"]-[aux1]
# todo lo que nos queda por hacer es calcular las horas diurnas que, lógicamente, son la diferencia entre las horas trabajadas y las horas nocturnas
# Finalmente eliminamos todas las columnas auxiliares que creamos y dejamos solamente aquellas que queremos que aparezcan en nuestro informe
Transformamos las columnas de horas al tipo de dato "Hora" y cargamos la consulta a una hoja del cuaderno
Para crear un informe que resuma los datos por empleados empezamos por crear una nueva consulta que cuyo origen es una referencia a la que acabamos de crear. Cambiamos el tipo de datos de las columnas de horas a número decimal y aplicamos "Agrupar por" de esta manera
Cargamos la consulta a una hoja del cuaderno y cambiamos el formato de las columnas de las horas al tipo personalizado [hh]:mm en el menú de formato de celdas de Excel (ver la explicación en la nota anterior).
Nuestro resumen se verá así
muy bueno.
ResponderBorrar