Agrupación o resumen de filas

En Power Query, puede agrupar los valores de varias filas en un solo valor agrupando las filas según los valores de una o varias columnas. Puede elegir entre dos tipos de operaciones de agrupación:

• Agrupaciones de columnas.

• Agrupaciones de filas.

Para este tutorial, usaremos la siguiente tabla de ejemplo.

Tabla con columnas que muestran Año (2020), País (EE.UU., Panamá o Canadá), Producto (Camisa o Pantalones), Canal de ventas (En línea o Reseller) y Unidades (varios valores de 55 a 7500)

Dónde encontrar el botón Agrupar por

Puede encontrar el botón Agrupar por en tres lugares:

• En la pestaña Inicio, en el grupo Transformar.

  

• En la pestaña Transformar, en el grupo Tabla.

  

• En el menú contextual al hacer clic con el botón derecho para seleccionar columnas.

  

Uso de una función de agregado para agrupar una o varias columnas

En este ejemplo, el objetivo es resumir las unidades totales vendidas en el nivel de país y canal de ventas. Usaremos las columnas País y Canal de ventas para realizar la operación de agrupar por.

1. Seleccione Agrupar por en la pestaña Inicio.
2. Seleccione la opción Avanzadas para que pueda seleccionar varias columnas por las que agrupar.
3. Seleccione la columna País.
4. Seleccione Agregar agrupación.
5. Seleccione la columna Canal de ventas.
6. En Nuevo nombre de columna, introduzca Total de unidades, en Operación, seleccione Suma y, en Columna, seleccione Unidades.
7. Seleccione Aceptar.

Esta operación proporciona la tabla siguiente.

Operaciones disponibles

Con la función Agrupar por, las operaciones disponibles se pueden clasificar de dos maneras:

• Operación en el nivel de fila
• Operación en el nivel de columna

Estas operaciones se describen en cada una de estas operaciones.

Nombre de operación	Category	Descripción
Sum	Operación de columna	Suma todos los valores de una columna
Media	Operación de columna	Calcula el valor medio de una columna
Valor medio	Operación de columna	Calcula la mediana de una columna
Mín.	Operación de columna	Calcula el valor mínimo de una columna
Máx.	Operación de columna	Calcula el valor máximo de una columna
Percentil	Operación de columna	Calcula el percentil, utilizando un valor de entrada de 0 a 100, desde una columna.
Contar valores distintos	Operación de columna	Calcula el número de valores distintos de una columna
Count : contar filas	Operación de fila	Calcula el número total de filas de un grupo determinado
Contar filas distintas	Operación de fila	Calcula el número de filas distintas de un grupo determinado
Todas las filas	Operación de fila	Genera todas las filas agrupadas en un valor de tabla sin agregaciones

Nota:

Las operaciones de Contar valores distintos y Percentil solo están disponibles en Power Query Online.

Realizar una operación para agrupar por una o varias columnas

A partir del ejemplo original, en este ejemplo creará una columna que contiene las unidades totales y otras dos columnas que le proporcionan el nombre y las unidades vendidas para el producto de mayor rendimiento, resumidos en el nivel de país y canal de ventas.

1. Use las columnas siguientes como columnas de Agrupar por:

   • Country
   • Sales Channel

2. Cree dos nuevas columnas siguiendo estos pasos:

   1. Agregue la columna Unidades mediante la operación Suma. Asigne a esta columna el nombre de Total de unidades.
   2. Agregue una nueva columna de Productos mediante la operación Todas las filas.

   

Una vez completada la operación, observe cómo la columna Productos tiene valores de [Table] dentro de cada celda. Cada valor de [Table] contiene todas las filas agrupadas por las columnas de País y Canal de ventas de la tabla original. Puede seleccionar el espacio en blanco que hay dentro de la celda para obtener una vista previa del contenido de la tabla en la parte inferior del cuadro de diálogo.

Nota:

Puede que el panel de vista previa de detalles no muestre todas las filas que se usaron para la operación de agrupar por. Puede seleccionar el valor [Table] para ver todas las filas relativas a la operación de agrupación por correspondiente.

A continuación, debe extraer la fila que tiene el valor más alto en la columna Unidades de las tablas dentro de la nueva columna Productos y llamar a esa nueva columna Producto de mayor rendimiento.

Extracción de la información del producto de mayor rendimiento

Con la nueva columna Productos con valores de [Table], cree una nueva columna personalizada; para ello, vaya a la pestaña Agregar columna de la cinta de opciones y seleccione Columna personalizada en el grupo General.

Asigne a la nueva columna el nombre de Producto de mayor rendimiento. Escriba la fórmula Table.Max([Products], "Units" ) en Fórmula de columna personalizada.

El resultado de esa fórmula crea una nueva columna con valores de [Record]. Estos valores de registro son básicamente una tabla con una sola fila. Estos registros contienen la fila con el valor máximo de la columna Unidades de cada valor de [Table] de la columna Productos.

Con esta nueva columna de Producto de mayor rendimiento que contiene valores de [Record], puede seleccionar el icono de expandir , seleccionar los campos de Producto y Unidades y, a continuación, seleccionar Aceptar.

Después de quitar la columna de Productos y establecer el tipo de datos para las dos columnas recién expandidas, el resultado será similar a la siguiente imagen.

Agrupación aproximada

Nota:

La función siguiente solo está disponible en Power Query Online.

Para mostrar cómo realizar la agrupación aproximada, considere la tabla de ejemplo que se muestra en la imagen siguiente.

El objetivo de la agrupación aproximada es realizar una operación de agrupar por que usa un algoritmo de coincidencia aproximada para las cadenas de texto. Power Query usa el algoritmo de similitud Jaccard para medir la similitud entre pares de instancias. A continuación, aplica la agrupación en clústeres jerárquicos agregados para agrupar instancias. En la imagen siguiente se muestra la salida esperada, donde la tabla se agrupará mediante la columna de Persona.

Para realizar la agrupación aproximada, realice los mismos pasos descritos anteriormente en este artículo. La única diferencia es que esta vez, en el cuadro de diálogo Agrupar por, debe activar la casilla Usar agrupación aproximada.

Para cada grupo de filas, Power Query elegirá la instancia más frecuente como la instancia "canónica". Si se producen varias instancias con la misma frecuencia, Power Query elegirá la primera. Después de seleccionar Aceptar en el cuadro de diálogo Agrupar por, obtendrá el resultado que esperaba.

Sin embargo, puede elegir otras opciones para la operación de agrupación aproximada expandiendo las Opciones de agrupación aproximada.

A continuación se indican las opciones disponibles para la agrupación aproximada:

• Umbral de similitud (opcional): esta opción indica lo similares que deben ser dos valores para agruparlos. El valor mínimo de 0 hará que se agrupen todos los valores. El valor máximo de 1 solo permitirá agrupar los valores que coincidan exactamente. El valor predeterminado es 0,8.
• Omitir mayúsculas y minúsculas: al comparar cadenas de texto, se omitirán las mayúsculas y minúsculas. Esta opción está habilitada de forma predeterminada.
• Agrupar combinando partes de texto: el algoritmo intentará combinar partes de texto (como combinar Micro y soft en Microsoft) para agrupar valores.
• Mostrar puntuaciones de similitud: muestra puntuaciones de similitud entre los valores de entrada y los valores representativos calculados después de la agrupación aproximada. Requiere la adición de una operación como Todas las filas para mostrar esta información en un nivel de fila a fila.
• Tabla de transformación (opcional): puede seleccionar una tabla de transformación que asignará valores (como asignar MSFT a Microsoft) para agruparlos.

En este ejemplo, se usará una tabla de transformación para mostrar cómo se pueden asignar los valores. La tabla de transformación tiene dos columnas:

• De: cadena de texto que se va a buscar en la tabla.
• A: la cadena de texto que se va a usar para reemplazar la cadena de texto en la columna De.

En la imagen siguiente se muestra la tabla de transformación usada en este ejemplo.

Importante

Es importante que la tabla de transformación tenga las mismas columnas y nombres de columna mostrados anteriormente (tienen que ser "De" y "A"), de lo contrario, Power Query no los reconocerá.

Vuelva al cuadro de diálogo Agrupar por, expanda Opciones de agrupación aproximada, cambie la operación de Contar filas a Todas las filas, habilite la opción Mostrar puntuaciones de similitud y, a continuación, seleccione el menú desplegable Tabla de transformación.

Después de seleccionar la tabla de transformación, seleccione Aceptar. El resultado de esa operación proporciona la siguiente información:

En este ejemplo, se ha habilitado la opción Omitir mayúsculas y minúsculas, por lo que los valores de la columna De de la tabla Transformación se usan para buscar la cadena de texto sin tener en cuenta las mayúsculas y minúsculas de la cadena. Esta operación de transformación se produce primero y, a continuación, se realiza la operación de agrupación aproximada.

La puntuación de similitud también se muestra en el valor de la tabla junto a la columna Persona, que refleja exactamente cómo se agruparon los valores y sus respectivas puntuaciones de similitud. Puede expandir esta columna si es necesario o usar los valores de las nuevas columnas de Frecuencia para otros tipos de transformaciones.

Nota:

Al agrupar por varias columnas, la tabla de transformación realiza la operación de reemplazo en todas las columnas si al reemplazar el valor aumenta la puntuación de similitud.

Consulte también

Adición de una columna personalizada
Eliminación de duplicados