Cálculo de jornada que termina al día siguiente (Power Query) y despedida

Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, como podrán comprobar con un clic a la etiqueta "Fechas y horas" en la nube de etiquetas.

En particular el cálculo de duración de jornada laboral, con distintas variantes, en versiones Excel Clásico y también con Power Query.

Un problema que debemos resolver en este tipo de cálculos es cuando la jornada laboral concluye al día siguiente. En esta tabla, por ejemplo, restamos Salida de Entrada para calcular la duración de la jornada

En el caso de Roberto y de Cristina, Excel no puede calcular al diferencia. Excel no puede resolver diferencias de tiempo negativas. Como vimos, la mejor solución es usar la función RESIDUO()

También hemos visto, y recomendado, usar Power Query para este tipo de cálculos (y para muchos otros también). ¿Como solucionamos el problema en Power Query?

Creamos una consulta a la tabla del ejemplo (sin el cálculo de la duración) y restamos [Entrada] de [Salida]

Excel, Power Query y Covid-19

Aclaración importante: el objetivo de este post es mostrar las capacidades de Power Query y PowerPivot y de ninguna manera analizar o sacar conclusiones sobre la evolución de la pandemia.

Dicho esto veamos podemos analizar las estadísticas sobre el Coronavirus usando Power Query y PowerPivot.

Mi objetivo es combinar los datos que por lo general aparecen en páginas Web que se ocupan del tema, con otros datos como población (tamaño y densidad) y edad promedio. También generar indicadores como porcentaje de defunciones del total por millón habitantes, porcentaje de sanados del total de confirmados, porcentaje de defunciones del total de confirmados y otras más.

En este post no voy a mostrar en detalle el desarrollo del modelo, que se puede descargar aquí, sino solamente los lineamentos generales. Quien no esté interesado en los lineamentos del modelo puede ir directamente a la parte final del post para ver algunos de los reportes que se pueden crear.

Aclaración sobre la descarga del archivo: el archivo está ligado dinámicamente a las fuentes de datos; cada vez que se abre el archivo los datos se actualizan por lo que la apertura del archivo puede durar un poco más de los esperado. El cuaderno fue desarrollado usando Excel 365; en otras versiones puede no funcionar o no actualizar los datos.

1 - Las fuentes de los datos.

• Wikipedia: en esta página de Wikipedia se encuentra la tabla por localidad con los casos confirmados, los recuperados y las defunciones.
• Worldmeter: en esta página encontramos una tabla con datos de población, superficie, edad promedio y más por país.

Whatsapp y Power Query en tiempos del corona

En la última edición de las tablas de Moisés, corregida y aumentada debido al virus Corona, se ha agregado un nuevo mandamiento: "No reenviarás mensajes, chistes, videos y similares en vano".
Quien más, quien menos, todos pecamos; por aburrimiento, supongo.

En uno de mis grupos Whatsapp alguien se quejó de la cantidad de mensajes y videos reenviados, muchos de los cuales son "fakes", como por ejemplo el poema atribuido a Mario Benedetti (que fue escrito por Alexis Valdez) o que se puede prevenir la infección haciendo gárgaras con bicarbonato de sodio. Buena parte de la discusión giró en torno a quien mandaba más mensajes al grupo. Las respuestas fueron, como era de esperar, "yo no".

Para dirimir la cuestión había que contar cuantos mensajes mandó cada uno al grupo. Una posibilidad era hacer el recuento manualmente lo que demandaría a alguien con la paciencia de un monje tibetano. Otra posibilidad era echar mano a Excel, más precisamente al Power Query. En este post voy a mostrar como hacerlo.

Empezamos por exportar el "chat" del grupo:

Otra técnica para comparar listas con Power Query

Dadas dos o más tablas, inevitablemente alguien querrá saber qué elementos tienen en común las tablas, o qué elementos no tienen en común. En este blog hemos explorado varias técnicas para comparar listas y hoy voy a presentar una más.

Supongamos esta tabla que muestra los productos vendidos en cada país en una año determinado:

Duración de procesos - versión mejorada

Dándole una releída al post sobre duración de procesos con Power Query, que publiqué hace dos días, detecté lo que no llamaría error pero sí debilidad del modelo. Ese modelo calcula el tiempo que se requerirá para completar un proceso en múltiplos de media hora. Ese parámetro está "hard coded".

Podemos mejorar el modelo dando la posibilidad de definir el grado de precisión del cálculo, por ejemplo 15 minutos o una hora.

Para hacerlo vamos a definir el parámetro precision en una celda, crearemos un nombre definido que se refiera a esa celda y crearemos a continuación una consulta al rango (la celda) de manera que podamos usarla como variable en los cálculos del Power Query.

Duración de procesos con turnos - versión Power Query

En un comentario en el post sobre duración de procesos por turnos me pregunta un lector cómo hacer el cálculo tomando en cuenta solamente días laborales y con la posibilidad que la jornada laboral de cada día de la semana pueda ser distinta.

El modelo del post mencionado funciona exclusivamente con fórmulas que, si bien no puede decirse que sean muy complicadas, si requieren una buena medida de experiencia con Excel.

Si a la restricción de los días hábiles le agregamos la posibilidad que cada día tenga una jornada laboral distinta, el grado de complejidad se incrementa exponencialmente. Por lo tanto es importante decidir qué herramienta del arsenal de Excel usar (fórmulas, Vba, Power Query, PowerPivot).

En mi opinión Power Query y PowerPivot han "destronado" al Vba (no digo que se haya vuelto irrelevante pero el nuevo Excel ofrece mejores soluciones para muchos de los problemas que en el pasado solíamos resolver con Visual Basic). Para el caso que nos ocupa voy a utilizar Power Query.

Bien, suficiente introducción; vayamos al grano. Nuestra tarea es:

1 - dadas la fecha y hora del comienzo del proceso y la duración estimada del proceso, en horas;

2 - una definición de días laborales con comienzo y final de cada día (que pueden ser variables)

3 - calcular la fecha de finalización de la tarea/proyecto

Los elementos del modelo son:

Promedio del total acumulado con Excel

En relación a un viejo post sobre totales acumulados con tablas dinámicas (del 2009) un lector me consulta: "Este es el acumulado de SUMA..¿Hay alguna posibilidad de obtener el acumulado de PROMEDIOS?
La intención es calcular el promedio del total acumulado en una tabla dinámica así como podemos calcular el total acumulado.

Todo, o casi todo, se puede hacer con Excel pero a su vez, hay más de una forma de hacerlo. La herramienta a usar depende de una serie de factores como el tamaño de base de datos, la configuración de los datos, tarea repetitiva o no, etc. Las herramientas que Excel pone a nuestra disposición son (desde la versión 2010 en adelante):

• fórmulas;
• tablas dinámicas;
• Power Query;
• PowerPivot.

Fórmulas

Calendario con horas en Power Query

Ya he mencionado que mi abuelita solía decir que cuando tenemos un martillo todo problema se parece a un clavo. Power Query es mi "martillo" a la hora de resolver un problema de transformación de datos en Excel.

El "clavo" de turno era crear un calendario, similar al del post anterior, pero con horas. Es decir un fila para cada hora de cada día. En lugar de 365 o 366 filas, 8760 o 8784 si el año es bisiesto.

El método es similar al del post mencionado pero con ciertos cambios. Como queremos que nuestra solución sea dinámica, empezamos por definir un conexión a una celda donde ponemos el año para el cual queremos crear el calendario

Esta consulta la guardamos como "solo conexión" (ver el post anterior). El código es

Crear un calendario con Power Query

Supongamos que queremos crear un calendario con Power Query (¿por qué queríamos hacer semejante cosa? Bien, hay varios motivos pero no son el tema de este post).

Aparentemente la tarea es bien sencilla. Power Query tiene un "atajo" para crear Listas: {x..y} donde x e y son números enteros. Como sabemos, las fechas están representadas por números enteros de manera que si sabemos que el 01/01/2020 es el número 43831 y el 44196 el 31/12/2020, hacemos lo siguiente:

1 - abrimos una consulta en blanco

2 - en la barra de las fórmulas escribimos ={43831..44196} lo cual crea un serie aritmética de razón 1

Clasificación (ranking) ordinal con desempate aleatorio

En mi post anterior sobre clasificación (ranking) con Excel y con Power Query mostré un método de desempate basado en un "sorteo", es decir, asignar a cada miembro de la lista un número aleatorio y determinar la posición en caso de empate de acuerdo a ese número.

Como vimos, si usamos la función Number.Random de Power Query todas las filas de la tabla reciben el mismo número.

Buscando en la Internet dí con esta discusión donde se muestran algunas soluciones (aunque la única que funcionó con mi ejemplo fue a basada en List.Random).

Debo confesar que no entiendo cabalmente la solución propuesta que muestro en el post. Esto me produjo un sentimiento de incomodidad que me llevó a buscar mi propia solución.

Recordemos el problema a solucionar:

• tenemos un a lista de calificaciones de alumno;
• queremos ordenarla por calificación, de mayor a menor;
• en caso de "empate" (dos o más alumnos con la misma calificación), realizamos un sorteo para determinar la posición relativa de los "empatados";
• el sorteo lo hacemos calculando un número aleatorio para cada alumno. Luego ordenamos los empates de acuerdo al número aleatorio que recibió cada alumno.

La solución que desarrollé esta basada en la función Number.Random y en un "truco": combinar la consulta consigo misma.

Estos son los pasos aplicados partiendo de esta tabla de datos:

Ranking con Excel y con Power Query

Un docente me consulta sobre como ordenar una lista de alumnos de acuerdo a sus calificaciones. El problema del docente era cómo determinar la posición (ranking) de cada alumno en caso de empate (es decir, dos o más alumnos con la misma calificación).

Según Wikipedia hay cinco métodos de calcular la posición de un elemento en una lista (ranking)

• Ordinal simple (1,2,3,4): cada elemento recibe un número de clasificación secuencial sin tomar en cuenta "empates".
• Competencia estándar (1,2,2,4): los elementos que "empatan" reciben el mismo número de clasificación, y luego se deja un hueco en los números de clasificación.
• Competencia modificada (1,3,3,4): la clasificación se hace dejando los huecos en los números de clasificación antes de los conjuntos de elementos de igual rango (en lugar de después de ellos como en la clasificación estándar de la competencia).
• Clasificación Densa (1,2,2,3): los artículos que se "empatan" reciben el mismo número de clasificación, y los siguientes artículos reciben el número de clasificación inmediatamente posterior. 
• Clasificación fraccionada (1,2.5,2.5,4): los artículos que "empatan" reciben el mismo número de clasificación, que es la media de lo que tendrían en las clasificaciones ordinales. 

En un próximo post volveré sobre el tema de los métodos de ranking. En este post me centraré en el método ordinal simple y algunas variaciones. Digamos que ésta sea la lista de alumnos:

Determinar posición por grupos (Ranking) con Power Query

Esta nota viene a colación de una consulta sobre cómo calcular posiciones (ranking) por grupos con Power Query, tema sobre el cual publiqué en el pasado este post mostrando como hacerlo con Excel.

La consulta original era, dada una serie de datos agrupados por grupos, cómo calcular la posición (ranking) de cada elemento dentro del grupo usando Power Query.

Consideremos esta tabla de datos

Nuestra tarea es ordenar por volumen de ventas y de mayor a menor, las ventas mensuales en cada área.

Como toda tarea a realizar con Power Query empezamos por crear una consulta

Algunas cavilaciones sobre Excel, Power Query y fechas

Los cálculos de tiempo en Excel tienen sus bemoles como ya hemos visto por ejemplo en el post anterior. Excel (y no sólo Excel) usa una serie de números enteros para representar las fechas. Excel tiene dos años-base distintos para los cálculos de tiempo: 1900 y 1904 de manera que el 1 representa el 01/01/1900 o el 01/01/1904 según el caso.
Fueran cuales fueran las razones (ver el comentario de Jaime en el post mencionado) la base 1900 conlleva un serio problema: Excel considera al año 1900 como bisiesto cuando no lo es (y recordemos que por defecto la base de cálculo es 1900).
Podemos ver este bug haciendo este ejercicio:

1. creamos una columna con una serie de números enteros del 31 al 61;
2. copiamos esta serie en la columna contigua.
3. en la segunda columna cambiamos el formato a Fecha.

Vemos la correspondencia entre el número de serie (31, por ejemplo) y la fecha (31 de enero 1900). Si nos fijamos en el final de nuestra serie vemos el problema: Excel reconoce la fecha 29 de febrero 1900 

pero esta fecha ¡no existe! 1900 no es una año bisiesto. 

El problema con este bug es que influye en el cálculo de lapsos. Por ejemplo si calculamos 

01/02/1900 - 31/01/1900

obtenemos, naturalmente 1; pero si calculamos 

01/03/1900 - 28/02/1900

obtenemos, erróneamente, 2.

Es fácil entender por qué Microsoft nunca corrigió este bug.¿Cuántas veces tuvieron que calcular diferencias de fechas que involucren el año 1900?.

¿Qué sucede en Power Query?

Total Móvil Anual (TAM) con PowerPivot

Como ya he declarado alguna vez en el pasado, lo prometido es deuda. A un poco más de dos meses de haber publicado el segundo post sobre como calcular el total anual móvil donde prometí mostar como calcularlo con PowerPivot, paso a saldar mi deuda (mis pocos pero buenos lectores sabrán disculpar).

PowerPivot es imbatible en el área de cálculos que impliquen comparación de períodos (lo que en inglés se da por llamar "Time Intelligence"). Y lo vamos a mostrar con el tema que estamos tratando, el cálculo del total anual móvil.

En este post parto de la suposición que el lector tiene un  conocimiento adecuado de PowerPivot y en particular del lenguaje DAX, y si no es así, espero que esta nota sirva de aliciente para impulsarlo a estudiar estas herramientas.

Vamos al grano. Para este ejemplo vamos a usar una tabla de datos diarios (ventas) del período 2015-2018.

Nuestra tarea es calcular el total anual móvil (TAM) para cada mes, es decir, totalizar el total mensual y compararlo con el acumulado de los últimos doce meses. Luego podemos calcular el promedio mensual del TAM y compararlo con las ventas del mes.

Total Móvil Anual (TAM) con Power Query

En el post anterior vimos como calcular el Total Anual Móvil usando fórmulas en Excel Clásico y prometimos mostrar como hacerlo con Power Query. Lo prometido es deuda, así que aquí va la explicación.

Empezamos por la misma tabla de datos de la nota anterior

Para calcular el TAM de Enero tenemos que sumar las ventas desde febrero del 2017 a enero del 2018 incluido, es decir los últimos doce meses. De la misma manera para febrero: desde marzo del 2017 a febrero del 2018 y así sucesivamente.

Veamos cómo creamos el informe con Power Query

Totales acumulados con Power Query

No me considero un profesional experto en Excel y aledaños (Power Query, PowerPivot), pero si un usuario experimentado. Por ese motivo las notas de este blog reflejan más mi trabajo cotidiano con esta fantástica herramienta que un enfoque técnico-profesional.

Una vez dicho ésto, vayamos al punto de la nota: en algún momento de nuestra vida tuvimos o tendremos que calcular totales o saldos acumulados ("running totals" en inglés).

En Excel Clásico (Excel Clásico = Excel sin Power Query y PowerPivot) sabemos hacerlo con fórmulas o, mejor aún, con tablas dinámicas. ¿Por qué, entonces, hacerlo con Power Query? Porque puede ser parte de una serie de transformaciones que estamos haciendo dentro de la consulta o porque, sencillamente, nos resulte más eficiente.

Sea cual sea el motivo vamos a ver cómo hacerlo en el entorno del Power Query.

Las trampas ocultas de Power Query

Confieso que el título de este post peca de sensacionalismo, pero es por una buena causa: mostrar que, si no prestamos la debida atención, las consultas que creamos con Power Query pueden fallar y cuando lo hacen, lo harán en el momento menos adecuado (por ejemplo cuando estamos demostrando las bondades del modelo que acabamos de construir a nuestro jefe o cliente).

Voy a mostrar a una de esas trampas: la trampa de dividir por columnas (basado en un hecho real 😊).

Esta tabla muestra en que países han realizados ventas algunos de nuestros agentes

Podemos ver que Steven a realizado ventas en 4 países , Michael en 5 y Anne también en 5.

Queremos organizar la tabla de manera que en la columna "País" aparezca un solo país por fila. Así tendremos 4 filas para Steven, 5 para Michael y 5 para Anna.

Damos los siguientes pasos:

Fechas anteriores al 1900 - versión Power Query

Como todos sabemos según Excel la historia comenzó el 1 de enero de 1900,. Es decir, Excel nos permite hacer cálculos de fechas sólo a partir de esa fecha. Cualquier intento de ingresar en una celda o hacer cálculos con fechas anteriores resultará en un error.
Hace once años publiqué un post con los posibles remedios a operaciones con fechas anteriores al 01/01/1900. Estas soluciones implican usar macros, como este que ofrece Microsoft para calcular edades con fechas anteriores al 1900, o instalar complementos.
¿Qué sucede en Power Query cuando queremos hacer cálculos con fechas anteriores al 1900? Supongamos esta tabla con las fechas de nacimiento y fallecimiento de hombres y mujeres de ciencia

En Excel las fechas anteriores al 1900 son interpretadas como texto y las posteriores como fechas (las posteriores al 1900 están alineadas a la derecha, como números, mientras que las anteriores a la izquierda).

Nuestra tarea es calcular las edades a las que fallecieron nuestros científicos. Vamos a ver que sucede cuando creamos una conexión a esta tabla en Power Query

Eliminar saltos de línea en celdas de Excel

Excel permite dividir textos ingresado a una celda en varias líneas usando Alt+Enter para separar las entradas. Por ejemplo

Las celdas A2 y A3 contienen cada una seis nombres.Esta técnica, que puede ser visualmente conveniente, genera muchos problemas cuando queramos usar estos datos. Por ejemplo si queremos asignar a cada nombre alguna característica como edad. Para poder hacerlo tenemos que deshacer los saltos de línea para tener cada nombre en  una celda separada.

En este post voy a mostrar como hacerlo con las herramientas de Excel Clásico y como hacerlo con Power Query.

Muestra aleatoria con Power Query

Para el ejemplo del post "BUSCARV o Combinar consultas" necesitaba crear una tabla con 10 productos elegidos aleatoriamente de una lista con más de 70 items. En Excel Clásico resolvemos la cuestión usando la función ALEATORIO() o la función ALEATORIO.ENTRE():

1. asignamos un número aleatorio a cada fila, 
2. cancelamos las fórmulas con Copiar--Pegar--Solo valores, 
3. ordenamos la tabla en forma ascendente o descendente 
4. elegimos las primeras 10 filas (o el número de filas que deseemos). 

¿Cómo lo haríamos con Power Query?  Contamos con la función Number.Random pero al usarla veremos que suceden cosas extrañas.

Veamos este ejemplo

Partimos de la lista completa, y creamos una consulta