Power Query para Excel – Capítulo 11 – Agrupar y Resumen
Excel cuenta con una amplia gama de herramientas para agrupar y resumir información, tales como Tablas dinámicas y Subtotales, sin embargo en este tutorial veremos una manera muy interesante de resumir información, y es, con la fabulosa herramienta de Power Query
Agrupar filas
En nuestro archivo de ejemplo tenemos una tabla de datos los cuales deseamos agrupar información. La agrupación que deseamos es en base a SUCURSAL y VENDEDOR, es decir, deseamos saber la cantidad de PRODUCTOS vendidos por cada VENDEDOR en cada SUCURSAL y saber la SUMA de productos vendidos.
Figura 1. El objetivo es agrupar por SUCURSAL y VENDEDOR y obtener los productos vendidos y la suma de ellos.
Con la Tabla seleccionada seguimos los siguientes pasos para agrupar las filas:
-
En la pestaña Datos, en la sección Obtener y transformar datos elegimos Desde una tabla o rango.
-
En la ventana del Editor de Power Query elegimos la pestaña Transformar > Agrupar por.
-
Elegimos la sección Avanzado.
-
Agregamos dos columnas para agrupar, SUCURSAL y VENDEDOR.
-
Ahora agregaremos dos columnas nueva para realizar agregaciones (operaciones).
-
Agregamos la columna CantidadProductos y en Operación elegimos Recuento de filas.
-
Agregamos la columna SumaVentas, en Operación elegimos Suma y en columna elegimos CANTIDAD.
-
Nos vamos a la pestaña Archivo > Cerrar y cargar.
-
Aceptar.
Figura 2. Agregamos columnas para agrupar y para realizar operaciones.
Ver video Power Query Agrupar y Resumen
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Las columnas SUCURSAL y VENDEDOR fueron las columnas por las cuales deseamos que se realice la agrupación de filas de la tabla. La columna CantidadProductos nos dará la cantidad de productos vendidos por sucursal y vendedor (ojo, no la suma de productos sino la cantidad de productos únicos), y la columna SumaVentas nos devolverá la suma de productos vendidos por ese vendedor en esa sucursal.
Figura 3. Agrupamos por SUCURSAL y VENDEDOR y obtenemos la cantidad de productos y la suma.
Resumen de filas
Haciendo los pasos anteriores logramos obtener un agrupamiento de filas en base a sucursal y vendedor, ahora lo que deseamos es saber cuál fue el producto más vendido por sucursal vendedor y la suma de ventas de ese producto.
-
Elegimos una celda del resultado de la consulta y nos vamos a la pestaña Consulta y elegimos Editar.
-
En el paso Filas agrupadas damos clic en el engrane.
-
Agregamos una nueva agregación, en Columna le ponemos Detalle y en Operación elegimos Todas las filas.
Figura 4. Añadimos una columna de agregación de Todas las filas.
Se añade una nueva columna llamada Detalle donde vemos el detalle de filas de cada agrupación. En lugar de ver los valores en cada celda de la columna Detalle vemos la palabra Table.
Ahora lo que deseamos es obtener el máximo de ventas de los productos que obtenemos en la columna Detalle. Por ejemplo, si en la columna Detalle tenemos 3 productos, entonces deseamos saber cuál de esos 3 productos fue el que más se vendió.
-
Debemos añadir una nueva columna para saber cuál fue el producto más vendido.
-
Nos vamos a la pestaña Agregar columna > Columna personalizada.
-
El nombre de la nueva columna será MáximoVentas.
-
La fórmula que usaremos será Table.Max([Detalle],”CANTIDAD”)
-
Aceptar.
Figura 5. Con Table.Max obtenemos el máximo de ventas de la columna CANTIDAD.
Ahora veremos dos columnas, Detalle donde veremos el detalle de productos y la columna Máximo ventas donde vemos el registro del productos que tuvo más ventas.
Figura 6. En la columna Detalla vemos la tabla de productos y en MáximoVentas vemos el registro del producto con más ventas.
-
En la columna MáximoVentas vemos un icono para expandir las columnas
-
Expandimos las columnas y elegimos solo las de PRODUCTO y CANTIDAD.
-
Desmarcamos la opción Usar el nombre de la columna original como prefijo.
-
Damos clic derecho en la columna Detalle y elegimos Quitar.
-
Nos vamos a la pestaña Archivo > Cerrar y cargar.
Figura 7. De los 3 productos, en Producto 19 tuvo más ventas: 275.
Descarga el archivo de ejemplo
Aprendamos Power Query en Excel – 11 – Agrupar y Resumen.zip
Si te gustó este tutorial por favor anótate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.
