Sumar cada enésima fila de una columna

Digamos que tiene una lista de transacciones de una sola columna. Desea sumar los valores de cantidad, que se encuentran en cada quinta fila. Esta es una tarea perfecta para Power Query. Entonces, en esta publicación, demostraré cómo configurar una consulta que elimine y mantenga un número definido de filas en un patrón repetido.

Objetivo

Antes de llegar demasiado lejos, permítanme compartir un par de imágenes para que podamos visualizar nuestra tarea. Primero, aquí hay un ejemplo de una lista de transacciones contenidas en una sola columna.

Notarás que es una secuencia repetitiva, donde el ID de la transacción es el primer elemento (p. ej., 107240), el primer nombre es el segundo elemento (p. ej., Jessica), el apellido es el siguiente y luego la calle, la ciudad y el estado. , zip… y luego la cantidad (por ejemplo, 223,5). Luego hay una celda en blanco y luego se repite el patrón.

Nuestro objetivo es sumar los valores de los montos de cada transacción, que es el octavo elemento de cada transacción. Entonces, antes de Power Query, ¿cómo abordaríamos esto? Mmm. Podríamos intentar hacer una serie de filtros. O tal vez copiar/pegar transposición especial en cada transacción. O tal vez escriba una fórmula para sumar o identificar condicionalmente los valores de la cantidad. Luego, una vez que hayamos finalizado estos pasos manuales, estamos bien. Hasta la próxima… entonces podremos realizar estos pasos nuevamente. Pero la buena noticia es que Power Query hace que este tipo de tarea sea muy fácil, esta vez y la próxima vez que tengamos una actualización.

Power Query nos ayudará a transformar la lista de transacciones y mantener solo los valores de monto, así:

Una vez que estemos aquí, es fácil calcular la suma. O, si lo deseamos, incluso podríamos hacer que Power Query calcule la suma y simplemente la devuelva, así:

Entonces, ahora que podemos ver nuestro objetivo, hagamos esto.

Detalles

Realizaremos esta transformación siguiendo estos pasos:

• Importar datos a Power Query
• Eliminar filas alternativas
• Calcular total

Hagámoslo.

Importar datos a Power Query

Utilice el comando Datos Obtener datos para localizar su lista de transacciones. En nuestro caso, está en una hoja de cálculo de Excel, por lo que usamos el comando Desde tabla/rango.

Podemos ver los datos ingresados ​​en el editor de Power Query, como se muestra a continuación.

Ahora es el momento de definir nuestro patrón.

Eliminar filas alternativas

Podemos ver que los valores de monto son el octavo elemento en cada transacción. Luego vemos un valor en blanco (nulo) después del monto. Y luego el patrón continúa. Es decir, hay 9 artículos en total en cada transacción porque necesitamos contabilizar el nulo. Entonces, al final del día, queremos eliminar los elementos del 1 al 7, conservar el elemento 8 y eliminar el elemento 9. Nuestra regla debe tener en cuenta todos los elementos del patrón repetitivo. ¿Bien? Muy bien, simplemente hacemos clic en Inicio Eliminar filas Eliminar filas alternativas . El cuadro de diálogo resultante se muestra a continuación.

Le indicamos a Power Query que comience en la fila 1, que elimine 7 elementos y luego conserve 2 elementos. Damos clic en Aceptar y bam:

Luego, podemos aplicar patrones adicionales si es necesario. En este caso particular, dado que los elementos que queremos eliminar son todos iguales (nulos), sería rápido simplemente aplicar un filtro. Pero, para demostrar la idea de aplicar un patrón adicional, simplemente hacemos clic nuevamente en el comando Eliminar filas alternativas .

Esta vez, la primera fila que queremos eliminar está en la fila 2, y luego en filas alternas (por lo tanto, elimine 1 y conserve 1). Bam:

Y ahora tenemos una lista limpia de las cantidades justas. En este punto, podríamos enviar la lista de cantidades nuevamente a Excel para poder sumarlas haciendo clic en Cerrar y cargar en… tabla. Pero ciertamente también podemos hacer que Power Query calcule la suma.

Nota: puede seguir aplicando patrones adicionales si es necesario utilizando el comando Eliminar filas alternativas varias veces.

Calcular el total

Para calcular la suma, simplemente hacemos clic en Transformar Estadísticas Suma . Bam:

Luego simplemente cerramos y cargamos en… tabla, y bam:

Pero quizás lo mejor de todo es que mañana, cuando obtengamos una nueva lista de transacciones, no necesitaremos volver a crear todos esos pasos. Todo lo que tenemos que hacer es hacer clic derecho en la tabla de resultados y Actualizar

Si tiene otros consejos relacionados con Power Query, compártalos publicando un comentario a continuación.

Archivo de muestra: RemoveAlternate.xlsx