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

Power Query – El caso de los espacios rebeldes

Empiezo con una aclaración: todo el crédito de esta nota corresponde a KenPuls que hace dos años atrás publicó la solución al problema que muestro en este post.

Como sabemos Power Query es la mejor herramienta para extraer y transformar datos. Una bendición para los usuarios de Excel que trabajamos con grandes cantidades de datos de distintas fuentes.

Una de las grandes ventajas del Power Query es permitirnos realizar todo tipo de transformaciones, algunas realmente complejas, desde la interface del usuario (UI).

Una de las transformaciones usuales es dividir una columna usando el espacio entre los valores de la celda como criterio.

En Excel (en este ejemplo uso Excel 2013), por ejemplo, podemos usar usamos la funcionalidad Datos-Obterner Datos Extarnos-Dede texto lo que abre el asistente

Podemos ver claramente que el archivo tiene 6 columnas pero al observar con atención veremos que entre las columnas hay más de un espacio. Y, además, la cantidad de espacios entre cada columna no es constante. Sin embargo ésto no es un problema para el asistente de importar texto

Al señalar "Espacio" como separador Excel ignora todos los espacios inncesarios yrealiza la división en la forma deseada.

Power Query tiene, tal como Excel, esa posibilidad desde la interfaz del usuario (el menú) pero el comportamiento es distinto. Empezamos por crear la consulta editándola en el editor de PQ y usando "Dividir columna" con el separador Espacio

El resultado es totalmente distinto del esperado!!!

Esto se debe a que Power Query, a diferencia de Excel, interpreta cada espacio como separador. A esta altura de los acontecimientos volvemos sobre nuestros pasos echamos manos a Transformar-Recortar (Trim, el equivalente de la función ESPACIOS)

pero si lo intentan verán que tampoco ésto nos ayudará en la tarea. El problema es que Recortar (Trim) del PQ sólo elimina los espacios finales! 

Aquí es donde Ken Puls viene a nuestro rescate. En la nota que menciono al principio, Ken publica una solución basada en una función de PQ.

Sin más preámbulos, vamos a mostrar como usar la función. En el menú de Power Query iniciamos una consulta en blanco

En la ventana del PQ abrimos el editor avanzado

borramos todo el contenido y pegamos este código

(text as text, optional char_to_trim as text) =>

let
char = if char_to_trim = null then " " else char_to_trim,
split = Text.Split(text, char),
removeblanks = List.Select(split, each _ <> ""),
result=Text.Combine(removeblanks, char)
in
result

y presionamos el botón Listo

En la ventana del editor cambiamos el nombre de la función a algo más significativo, como  PowerTrim

Apretamos Cerrar y Cargar; la función aparece ahora en la ventana de las consultas.

Ahora volvemos a abrir la consulta de los datos, creamos una columna personalizada donde la función ha eliminado los espacios inneesarios; borramos la columna original y aplicamos "Dividir columna" a la columna que acabamos de crear. Todo el proceso puede verse en este video