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