Cursos
Módulo
Uso de funciones integradas y GROUP BY en Transact-SQL - Training
Uso de funciones integradas y GROUP BY en Transact-SQL
Este explorador ya no se admite.
Actualice a Microsoft Edge para aprovechar las características y actualizaciones de seguridad más recientes, y disponer de soporte técnico.
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.
Captura de pantalla de una 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 Revendedor) y Unidades (varios valores de 55 a 7500)
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 se seleccionan las 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.
Esta operación proporciona la tabla siguiente.
Con la función Agrupar por, las operaciones disponibles se pueden clasificar de dos maneras:
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.
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.
Use las columnas siguientes como columnas de Agrupar por:
Cree dos nuevas columnas siguiendo estos pasos:
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.
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 Productos y establecer el tipo de datos para las dos columnas recién expandidas, el resultado será similar a la siguiente imagen.
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 agrupa mediante la columna 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 elige la instancia más frecuente como la instancia "canónica". Si se producen varias instancias con la misma frecuencia, Power Query seleccione 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:
En este ejemplo, se usa una tabla de transformación para mostrar cómo se pueden asignar los valores. La tabla de transformación tiene dos columnas:
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 que se muestran en la imagen anterior (tienen que etiquetarse como "De" y "A"). De lo contrario, Power Query no reconoce la tabla como una tabla de transformación.
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.
Para obtener más información sobre cómo funcionan las tablas de transformación, vaya a Prescripciones de tabla de transformación.
Adición de una columna personalizada
Eliminación de duplicados
Cursos
Módulo
Uso de funciones integradas y GROUP BY en Transact-SQL - Training
Uso de funciones integradas y GROUP BY en Transact-SQL