Cómo utilizar Power Query Group By para resumir datos
En esta publicación, analizamos la transformación Grupo por Power Query. Agrupar por resume los datos agregando filas en función de una o más columnas.
En Excel, hay muchas formas de agrupar y resumir datos. Podríamos utilizar técnicas como:
- Funciones: funciones de matriz dinámica junto con SUMIF(S), SUMPRODUCT o AGGREGATE .
- Tablas dinamicas
- Característica de subtotal (subtotal de datos)
En Power Query, Agrupar por realiza una tarea similar, pero el propósito es ligeramente diferente. En Excel, generalmente resumimos para mostrar un resultado; sin embargo, en Power Query, resumimos para reducir los datos al nivel de granularidad óptimo.
La transformación Power Query Group By es simple pero poderosa. Entonces empecemos.
Tabla de contenido
- Guión
- Dos vistas de Power Query Group por
- Grupo básico por
- Grupo avanzado por
- Resumir texto
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0116 Grupo de Power Query por.xlsx
Tener acceso
El archivo de ejemplo contiene solo una tabla. La tabla incluye datos de ventas de enero de 2019 con las columnas Fecha, Cliente, Producto, Vendido por y Valor.
Seleccione una celda en la tabla y haga clic en Datos de la tabla/rango en la cinta para cargar los datos en Power Query.
Los datos se cargan en el editor de Power Query.
La función Agrupar por se encuentra en dos lugares:
- Cinta de inicio: Grupo de inicio por
- Cinta de transformación: Transformar grupo por
Elija cualquiera de las opciones para abrir el cuadro de diálogo Agrupar por .
Dos vistas de Power Query Group por
El cuadro de diálogo Agrupar por viene en dos formas:
- Básico: permite resumir una sola columna y una sola agregación por
- Avanzado: permite múltiples agregaciones basadas en múltiples columnas
La vista inicial depende del número de columnas seleccionadas en Power Query. Si se selecciona una columna dentro de la ventana de vista previa, el cuadro de diálogo muestra de forma predeterminada la vista básica; de lo contrario, se abre la vista avanzada.
Para cambiar entre las vistas básica y avanzada, haga clic en los botones de opción del cuadro de diálogo.
Examinaremos ambas vistas en esta sección.
Grupo básico por
Para mostrar las opciones básicas, asegúrese de que el botón Básico esté seleccionado.
En nuestro ejemplo, supongamos que queremos resumir nuestros datos para mostrar el valor promedio de venta del producto. Seleccione las siguientes opciones: (se muestran en la captura de pantalla a continuación).
- Columna para agrupar por: Producto
- Nuevo nombre de columna : Venta promedio
- Operación : Promedio
- Columna : Valor
Haga clic en Aceptar para cerrar el cuadro de diálogo y realizar la transformación.
La ventana de vista previa se ve así:
La tabla muestra los artículos únicos de la columna Producto y el valor de venta promedio de cada uno.
En el ejemplo, utilizamos la agregación promedio. El menú desplegable de operaciones tiene las siguientes opciones:
- Suma
- Promedio
- Mediana
- mín.
- máx.
- Contar filas
- Contar filas distintas
- Todas las filas (que crea una tabla de cada elemento agrupado)
Grupo avanzado por
Haga clic en el botón Avanzado en el cuadro de diálogo para mostrar las opciones avanzadas.
Avanzado nos permite resumir y calcular en base a tantas columnas como necesitemos.
Para este ejemplo, digamos que queremos el total y el recuento de transacciones de ventas por producto y cliente.
Configure la primera sección para incluir las columnas Producto y Personalizado . Haga clic en el botón Agregar agrupación para agregar columnas, o en los tres puntos ( … ) para eliminar columnas.
En la sección de nombre de nueva columna , aplique la siguiente configuración:
- Nuevo nombre de columna: Ventas totales
- Operación: Suma
- Columna: Valor
Haga clic en el botón Agregar agregación y luego configure la nueva columna de la siguiente manera
- Nuevo nombre de columna: Transacciones
- Operación: contar filas
- Columna: [no es necesario para Contar filas]
Haga clic en Aceptar para realizar la transformación.
La ventana de vista previa ahora se ve así:
La tabla muestra los elementos únicos de las columnas Producto y Cliente con los operadores Suma y Recuento aplicados. Si bien cada columna puede contener duplicados, la combinación de columnas es única.
Resumir texto
En la lista de operaciones en el cuadro de diálogo, solo hay transformaciones numéricas (Suma, Promedio, Mín, etc.). Sin embargo, también podemos realizar agregaciones de texto. Sólo necesitamos hacer algunas ediciones en el código M generado.
Supongamos que queremos una lista de cada persona que vendió a cada cliente. Aplique los siguientes parámetros en el cuadro de diálogo Agrupar por:
Al hacer clic en Aceptar , se genera un error en la columna Ventas por , ya que no se puede realizar la suma en un valor de texto.
Sin embargo, en el código M, podemos cambiar esto:
= Table.Group(#"Changed Type", {"Customer"}, {{"Sales By", each List.Sum([Sold By]), type nullable text}})A esto:
= Table.Group(#"Changed Type", {"Customer"}, {{"Sales By", each Text.Combine(List.Distinct([Sold By]),","), type nullable text}})Ahora tenemos una lista de vendedores para cada cliente.
Esto demuestra que aunque las operaciones de texto no aparecen en la interfaz de usuario, aún podemos editar el código M para lograr este resultado.
Conclusión
Esperemos que esté de acuerdo en que la transformación Grupo por Power Query es una característica simple pero poderosa.
Hemos visto que funciona con números y texto (si hacemos algunos cambios en el código M).
Power Query prepara principalmente datos para su uso en Excel o en un modelo de datos. Por lo tanto, tendemos a usar Agrupar por para reducir la cantidad de información al nivel más bajo de granularidad requerido, en lugar de crear informes.
Para ver otros ejemplos de Agrupar por, consulte estas publicaciones:
- Power Query: combina filas en una sola celda
- Power Query: porcentaje del total o categoría
- Power Query: valor de búsqueda en otra tabla
Para conocer algunas técnicas avanzadas de Agrupar por, mira este vídeo: https://www.youtube.com/watch?v=jLpgt-wptH4
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta