Acumulado día a día, y, mes a mes.

En este ejemplo vamos a ver como podemos calcular un acumulado en Power Query, tenemos el siguiente modelo.

Vamos a llevarlo a Power Query, con una celda dentro del modelo, vamos a la pestaña de datos, dentro del grupo obtener y transformar, hacemos clic en de una tabla o rango.

Vemos que en la columna fecha, aparece la hora, pero no tenemos, por lo que vamos a quitarla, para ello, desde la pestaña de inicio, teniendo la columna seleccionada, desplegamos tipo de datos, y, seleccionamos fecha.

En la ventana que se abre, seleccionamos sustituir la actual.

Ya sabemos que el acumulado es sumar la cantidad anterior con la siguiente, pero, vamos a crear un acumulado día a día, y, mes a mes, por lo que voy a necesitar una columna con el comienzo de cada mes, para ello, teniendo la columna fecha seleccionada, vamos a la pestaña agregar columna, dentro del grupo de fecha y hora, desplegamos fecha, desplegamos mes, y, seleccionamos inicio de mes.

Ahora, tenemos que referirnos a cada fila de la columna fecha, y, en toda la tabla.

Voy a añadir un paso haciendo clic en, agregar una columna personalizada.

Vemos que aparece el último paso.

Ponemos la función Table.AddColumn, que nos va a agregar una columna, el primer argumento es tabla, pues dejamos Inicio del mes insertado.

= Table.AddColumn(#"Inicio del mes insertado"

El siguiente argumento, es el nombre que le vamos a dar a la columna, ponemos acumulado entre comillas dobles.

= Table.AddColumn(#"Inicio del mes insertado", "Acumulado"

El siguiente argumento es con que columna vamos a trabajar, si pongo la palabra each y después la columna fecha entre corchetes, cierro paréntesis, y, acepto.

= Table.AddColumn(#"Inicio del mes insertado", "Acumulado", each [Fecha])

Obtengo una copia de la columna fecha.

La palabra each, significa para cada, quiere decir que va a trabajar con cada fila de la columna fecha, es decir, esta tomando cada fecha de la columna fecha.

Each hace más fácil el uso de funciones, porque podemos trabajar con cada valor de cada fila.

Recordar, que debemos de ir usa cantidad más la siguiente.

Como tenemos que hacer dos comparaciones, será mejor usar dos variables, voy a borrar la palabra each de la expresión, para crear una variable debemos de encerrarla entre paréntesis, como sigue:

= Table.AddColumn(#"Inicio del mes insertado", "Acumulado", (AA)

Para signarle un valor, dejamos un espacio, y, ponemos “=>”.

= Table.AddColumn(#"Inicio del mes insertado", "Acumulado", (AA) =>

Ahora, vamos a hacer uso de la columna total, que es la que nos interesa, pero para usar la columna total, primero debemos de poner el nombre de la variable, y, entre corchetes el nombre de la columna.

Cerramos paréntesis y aceptamos.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => AA[Total])

Obtenemos una copia de la columna total.

Ahora mismo, tenemos una tabla externa, y, otra interna, la tabla externa es:

Y, la interna:

Selecciono el paso, pulso F2, y, le pongo de nombre, acumulados.

En este punto, el calculo se complica un poco, porque tenemos que hacer dos comparaciones, y, para realizar dicha comparación, necesito seleccionar una columna, por lo que voy a usar la función Table.SelectRows, que devuelve una tabla de filas siempre que cumpla con la condición.

Con la función Table.Addcolumn, hemos añadido una columna a esta tabla externa, y, hará un calculo para cada fila, después, vamos a tener la función Table.SelectRows, entonces, para cada celda habrá una tabla, donde haremos ciertas acciones.

Pues continuemos…

Voy a dejar en la expresión hasta este punto:

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) =>

A continuación, voy a poner la función Table.SelectRows.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows

Abro un paréntesis, y, pongo la tabla.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows(“#"Inicio del mes insertado”

Esto quiere decir, que, en cada celda, habrá una tabla., y, realizará un cálculo fila a fila.

Coma, y, definimos una segunda variable.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows(“#"Inicio del mes insertado”, (BB)

Quiere decir que nos referimos a las columnas de la tabla copia.

Ponemos “=>”.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows(“#"Inicio del mes insertado”, (BB) =>

De la tabla interior, necesito la columna fecha.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows(“#"Inicio del mes insertado”, (BB) => BB[Fecha]

Ahora, necesito saber cuantas fechas son menores o igual al valor de cada fecha de la tabla externa.

Cerramos paréntesis de Table.SelectRows, y, de Table.Addcolumn.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows(“#"Inicio del mes insertado”, (BB) => BB[Fecha] <= AA[Fecha]))

Aceptamos, y tenemos una columna con tablas.

Si hago clic en la tercera tabla, en la parte inferior, vemos una tabla de tres columnas con las tres primeras fechas.

Pero, necesitamos trabajar con la columna de total, la función Table.SelectRows, es una tabla, en Power Query, podemos hacer una búsqueda bidireccional, y, queremos seleccionar una columna, vamos a usar el operador de acceso de campo, porque viene después de una tabla, y, obtiene la columna, entonces, después del cierre de paréntesis de Table.SelectRows, abrimos otro corchete, y, ponemos la columna de total, y, cerramos corchete.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => Table.SelectRows(“#"Inicio del mes insertado”, (BB) => BB[Fecha] <= AA[Fecha])[Total])

Podíamos preguntarnos, que AA[Fecha], es otro operador de campo, y, lo es, pero esta iterando dentro de Table.Addcolumn.

Aceptamos, y, ahora, tenemos una lista de valores.

Si hacemos clic en cualquier lista, en vez de las tres columnas, solo veremos la columna de total.

Tenemos una función llamada List.Sum que suma todos los valores que sean distintos a NULL, la ponemos antes de Table.SelectRows.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => List.Sum(Table.SelectRows(“#"Inicio del mes insertado”, (BB) => BB[Fecha] <= AA[Fecha])[Total]))

Cerramos paréntesis y aceptamos, y, ya tenemos nuestro acumulado.

Necesitamos un tipo de datos, porque no lo ha reconocido, para ello, antes del cierre de paréntesis, ponemos coma, y, type number, en minúsculas.

= Table.AddColumn(“#"Inicio del mes insertado”, "Acumulado", (AA) => List.Sum(Table.SelectRows(“#"Inicio del mes insertado”, (BB) => BB[Fecha] <= AA[Fecha])[Total]), type number)

Lo siguiente va a ser calcular el acumulado, pero mes a mes, por lo que tenemos que añadir una nueva columna, pero la expresión anterior nos va a vales, por lo que la seleccionamos, y, copiamos, e, insertamos un nuevo paso.

Para añadir una nueva columna, debemos de referirnos al último paso, por lo que borramos #"Inicio del mes insertado", y, ponemos el ultimo paso.

= Table.AddColumn(Acumulado

Como nombre de columna, le voy a poner:

= Table.AddColumn(Acumulado, "Acumulado mes a mes",

El resto de la expresión es igual que la anterior.

Ponemos la columna fecha de la variable BB, es decir, la tabla interna.

= Table.AddColumn(Acumulado, "Acumulado mes a mes", (AA) => List.Sum(Table.SelectRows(#"Inicio del mes insertado", (BB) => BB[Fecha] <= AA[Fecha])[Total]), type number)

Ahora, vamos a agregar la segunda condición, para ello, antes del siguiente cierre de paréntesis.

Dejamos un espacio, y, ponemos la palabra and, en minúsculas.

Ponemos el operador de comparación mayor o igual.

= Table.AddColumn(Acumulado, "Acumulado mes a mes", (AA) => List.Sum(Table.SelectRows(#"Inicio del mes insertado", (BB) => BB[Fecha] <= AA[Fecha] and )[Total]), type number)

Vamos a comparar cada fecha de la tabla interna, es decir, de la variable BB, siempre que sea mayor o igual a cada fecha de inicio de mes la tabla externa, es decir, la variable AA.

= Table.AddColumn(Acumulado, "Acumulado mes a mes", (AA) => List.Sum(Table.SelectRows(#"Inicio del mes insertado", (BB) => BB[Fecha] <= AA[Fecha] and BB[Fecha] >= AA[Inicio del mes] )[Total]), type number)

Aceptamos, y, tenemos una columna con el acumulado mes a mes, vemos que cuando finaliza un mes, el acumulado se reinicia.

Selecciono el paso, pulso F2, y, le pongo el siguiente nombre.

Le cambio el nombre a la consulta duplicado, y, le pongo acumulados.

Cerramos y cargamos como una conexión.

Vamos a la ventana de consultas y conexiones, a la derecha, seleccionamos la consulta de acumulados, con botón alternativo de ratón, y, seleccionamos cargar en.

Se abre la ventana de importar datos, marcamos tabla, marcamos hoja de calculo existente, la colocamos al lado del modelo, y, aceptamos.

Ya tenemos nuestro modelo cargado en Excel, en formato de tabla, con el acumulado día a día, y, mes a mes.