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.

Supongamos esta tabla donde queremos calcular como se van acumulando las ventas de mes en mes

Como siempre el primer paso es crear la consulta

En PASOS APLICADOS vemos que PQ ha creado automáticamente el paso Tipo Cambiado. Por lo general es recomendable eliminar este paso pero en esta caso lo vamos a dejar.

El siguiente paso es agregar una columna Índice con base 1 (en Power Query la base es 0, es decir, el primer elemento de una colección recibe el número 0; en este ejemplo queremos que el primer elemento reciba el número 1)

Esta nueva columna Índice nos servirá para ir acumulando la suma de los elementos en [Valor].

A diferencia de Excel Clásico, en Power Query no podemos hacer cálculos a nivel de "celda" usando la interfaz de usuario. Para hacerlo tendremos que echar manos a la funciones M.
Estas funciones están divididas en grupos de acuerdo a los objetos sobre los cuales actúan (Table functions, List functions, Time functions, etc.). En nuestro caso tendemos que fijarnos en las funciones List (en Power Query List es como un sinónimo de Columna).
La función List.Sum nos permite sumar todos los elementos de la columna y nosotros queremos ir sumándolos parcialmente. Para eso vamos a recurrir a la función List.FirstN para indicar cuantos elementos, empezando por el primero, queremos sumar. Como hacemos en Excel Clásico, también aquí "anidamos" las funciones.
Agregamos una columna personalizada con esta fórmula

List.Sum(List.FirstN([Valor],[Índice]))

Al apretar Aceptar veremos que obtenemos una columna con el valor Error. Si hacemos un clic sobre Error veremos este mensaje

No muy explicativo. Para solucionar esto tenemos que indicarle a Power Query en que tabla se encuentra el campo [Valor].  Contrariamente a la intuición, la tabla es la creada en el paso anterior a la aplicación de la función, es decir #"Índice agregado".

Para poder trabajar más cómodamente vamos a cambiar el nombre del paso a algo abreviado, IndAgr, con un clic sobre el nombre del paso y Cambiar nombre.
Ahora editamos el paso Personalizada agregada lo que podemos hacer de tres maneras: con un clic en el engranaje a la derecha del paso que abre el formulario de columna personalizada; directamente en la barra de las fórmulas o en el editor avanzado.
En este ejemplo hacemos un clic en el engranaje para abrir el formulario y en la fórmula anteponemos IndAgr a [Valor]

Apretamos Aceptar y Aleluya!

Finalmente podemos eliminar la columna Índice y cargar la consulta en una hoja de Excel.


Por supuesto, hay más de una forma de obtener el resultado. En el blog de Ken Puls pueden consultar estas notas:

• usando Agrupar por y creando una función personalizada;
• usando las funciones List.Accumulate y List.Range