Cálculo de horas trabajadas con Power Query - segunda nota

En la nota anterior mostramos como calcular las horas trabajadas por un empleado a partir de un informe en una hoja de Excel usando Power Query. En un caso sencillo como el del ejemplo la ventaja de usar Power Query en lugar de Excel tradicional (fórmulas) pasará desapercibida para la mayoría de los usuarios que aún no hayan adoptado las nuevas herramientas de Excel (Power Query, PowerPivot).
En este post mostraré un escenario más complejo (más real) donde nuestra misión es consolidar informes mensuales de varios empleados en un único informe. Y ésto debe suceder automáticamente y con sólo apretar un botón.

En este ejemplo cada empleado envía un informe semanal de las horas trabajadas. En este caso tenemos tres empleados pero, por supuesto, podrían ser cinco, veinte o cien.
La misión es:

• calcular las horas trabajadas de cada empleado;
• descontar media hora de descanso/comida cada día excepto que el empleado haya trabajado menos de siete horas;
• crear un informe que totalice las horas netas trabajadas por cada empleado en la semana.

Empezamos por crear una carpeta para guardar los informes semanales de los empleados que recibimos en hojas de Excel, como ésta (al final del post hay un enlace al video que muestra todo el proceso)

Abrimos un cuaderno Excel y creamos una consulta que consolide todos los archivos (informes) guardados en la carpeta de los informes con Obtener Datos-Desde un archivo-Desde una Carpeta

Vamos a resistir la tentación de apretar el botón Combinar y en su lugar apretamos Editar

Eliminamos todas las columnas con excepción de Content y Name. Name contiene el nombre del archivo que es a su vez el nombre del empleado pero con la terminación .xlsx. Para quitarla y que nos nos quede sólo el nombre del empleado usamos Reemplazar

con este resultado

Ahora expandimos la columna Content (apretando la doble flecha a la derecha del encabezado) para combinar los archivos. En la ventana del editor veremos que la hoja contiene dos objetos

Elegimos "Tabla1" que es el rango que contiene la tabla de fechas y horas (más explicaciones en el video al final de este post donde muestro todo el proceso). Después de apretar Aceptar veremos esta tabla de datos combinados

 ¡La columna con los nombres de los empleados ha desaparecido! Para recuperarla hacemos un clic en el "engranaje" a la derecha del paso aplicado "Otras columnas quitadas1" y marcamos "Name"

y luego volvemos a seleccionar el último paso con un clic

Ahora nuestros datos están completos y podemos empezar a transformarlos. Empezamos por cambiar el nombre de la columna Name a Empleado. Como en el ejemplo del post anterior cambiamos el tipo de datos de Entrada y Salida a número decimal. Ésto nos permite realizar operaciones aritméticas sencillas con los datos evitando usar funciones DateTime y Time de Power Query con las que no estamos familiarizados.

y luego creamos la columna personalizada Horas Trabajadas 

La columna personalizada aparece con el formato "Cualquiera" por lo que tenemos que transformarlo a número decimal. La forma más fácil es con el menú Transformar-Tipo de Datos-número decimal.

Ahora tenemos que descontar media hora de cada fila donde el trabajador trabajo siete horas o más. Nuevamente agregamos una columna calculada con una fórmula condicional

Ahora eliminamos las columnas Entrada y Salida y cambiamos el tipo de datos de Horas Trabajadas y Horas Trabajadas Neto a Hora

Cargamos esta consulta a una hoja de Excel y tendremos un reporte detallado. Seguidamente vamos a crear un reporte agrupado por empleado.
Empezamos por crear una consulta cuyo origen es la consulta anterior usando Referencia en la ventana de las consultas

En la nueva consulta transformamos las columnas de la horas al tipo de datos número decimal y luego agrupamos por Empleado creando dos columnas: Horas Trabajadas - bruto y Horas Trabajadas - neto

con este resultado

Para que nuestro informe sea legible queremos que los datos aparezcan como horas, no como números decimales (en el video de la nota agrego una explicación sobre el tipo de datos Duración de Power Query).

Para expresar los datos como horas acumuladas usamos el  formato personalizado [hh:mm] en la tabla de la hoja

con el resultado esperado

Este video muestra todo el proceso




En el próximo post mostraré como hacer el cálculo tomando en dos turnos (diurno y nocturno).

Cálculo de horas trabajadas con Power Query

Cálculo de horas trabajadas es uno de los temas sobre los cuales recibo la mayor cantidad de consultas. Así que ha llegado el momento de ver cómo podemos realizar estos cálculos con Power Query.
Si ya hemos mostrada en varios posts como podemos calcular horas trabajadas, con y sin turnos, ¿por qué mostrar como hacerlo con Power Query? Por la misma razón por lo cual recomiendo a todo quien este dispuesto a escuchar:

• porque las soluciones con Power Query suelen ser más robustas;
• porque una vez que desarrollamos el modelo en Power Query no hace falta reescribir las fórmula y realizar los cálculos cada vez que que recibimos datos nuevos;
• porque podemos combinar datos de varios cuadernos o varias hojas automáticamente y con facilidad;
• porque Power Query es una de las herramientas centrales del "nuevo Excel" (Power Query, Power Pivot, Power BI).

Lista parcial :)

Y ahora, ¿cómo hacemos un cálculo de horas trabajadas con Power Query? En esta nota voy a mostrar un caso sencillo. Supongamos que recibimos de un trabajador (más adelante veremos cómo lo integramos con varios) una planilla semanal de horas trabajadas com ésta:

Tal como ya mostramos en posts anteriores, una cuestión a resolver es que a veces el turno termina al día siguiente del principio (en nuestro ejemplo el miércoles y el jueves). Ésto es crítico ya que no contamos con la fechas. La solución con fórmulas Excel es usar  RESIDUO (hay otras, pero ésta es la que recomiendo). Con Power Query vamos a hacerlo de otra manera.

Como toda solución en Power Query empezamos por crear una consulta a partir de la tabla de horas (para este ejemplo usaremos Excel 2016/365, pero funciona de la misma manera para Excel 2010/13) y visualizarla en el editor de consultas

Podemos ver que Power Query interpreta las horas como números decimales. Esto no debe sorprendernos ya que las fechas y horas son también en Excel números (enteros para las fechas y la parte decimal para las horas, minutos y segundos).
Por ahora no vamos a cambiar el tipo de datos. Para poder calcular las horas trabajadas creamos la columna "Horas Trabajadas" con esta fórmula

donde agregamos 1 (sumar un día) al valor de Salida cuando este es menor que el correspondiente de Entrada.

Ahora podemos eliminar las columnas Entrada y Salida, pero antes de cargar la consulta una hoja tenemos que cambiar el tipo de dato de la columna personalizada de "Cualquiera" a "Hora"

Agregamos la línea de Total con la función Suma a la tabla

y aplicamos el formato [hh]:mm

A partir de este momento cada vez que recibimos un reporte del trabajador todo lo que tenemos que hacer es reemplazar el reporte anterior en la carpeta y actualizar la consulta.

En el próximo post veremos como crear un reporte que combina los datos de varios trabajadores.

¿Quién se ha llevado mi asistente de importar texto?

El título del post es una paráfrasis del popular libro de Spencer Johnson de fines de los 90. El libro trata de las dificultades de afrontar cambios, tanto en el trabajo como en la vida privada. ¿Cómo está relacionado esto con Excel? Todo usuario veterano sabe muy bien a qué me refiero. A lo largo de los años Excel ha ido evolucionando y moviendo nuestro "queso" (la interface del usuario). Algunos cambios han sido revolucionarios y a su vez traumáticos para gran parte de los usuarios, como la cinta en lugar de las barras de menú en Excel 2007. Otros pasan inadvertidos, por ejemplo el cambio en el asistente de Tablas Dinámicas.

En Excel 2016 los asistentes de importar datos (el de texto entre ellos) no se encuentra en la cinta de opciones. En Excel 2007-2013 aparecían en la pestaña Datos bajo Obtener datos externos

 En Excel 2016 el usuario verá ésto

Es decir, el mecanismo de Power Query, bajo la denominación Obtener y Transformar Datos, ha reemplazado al asistente de importar texto (y otros tipos de archivos).

Hay mucha lógica en esta decisión de Microsoft y todo usuario de Excel obtendrá enormes beneficios de esta herramienta. Dicho ésto hay situaciones en las cuales queremos de todas maneras usar el "viejo" asistente de importar datos. Por ejemplo, en el caso de los espacios múltiples que mostré en la nota anterior.

El asistente no ha sido eliminado sino que no aparece en la cinta. Para poder utilizarlo tenemos que llevar a cabo las siguientes acciones:

1 - En Archivo-Opciones seleccionamos al sección Datos 

2 - Marcamos las opciones de nuestro interés, por ejemplo las cuatro que aparecen en la columna de la izquierda. Finalmente apretamos Aceptar.

Ahora esta opciones estarán a nuestra disposición bajo Datos-Obtener Datos-Asistentes Heredados