Ampliando las posibilidades de Agrupar en Power Query - primera nota

En varios posts he mostrado las posibilidades de Agrupar por de Power Query (por ejemplo, en este post). Como con muchas otras funcionalidades, Power Query nos permite realizar la agrupación de datos usando exclusivamente la interfaz del usuario. Es decir, no tenemos que conocer la sintaxis del idioma M (el motor de Power Query) ni las funciones aplicadas para lograr las transformaciones.
En este ejemplo disponemos de una tabla de ventas por países y productos

de la cual podemos obtener un informe que muestra el total de ventas y el valor de la venta promedio de cada país aplicando Agrupar por de esta manera

con este resultado

En este post voy a mostrar como extender las posibilidades de Agrupar por más allá de las ofrecidas en la interfaz del usuario editando manualmente parte del código creado automáticamente al aplicar los pasos.

Una aclaración: como siempre en Excel, hay más de una manera de resolver el ejercicio que desarrollaré a continuación; por ejemplo con Tablas Dinámicas. El objetivo de este post es mostrar como podemos extender las posibilidades de Power Query con unos pocos conocimientos del lenguaje M, un poco de intuición y otro poco de osadía.
Cuando aplicamos Agrupar por podemos elegir en la ventanilla Operación  alguna de estas ocho operaciones

Pero, como dije más arriba, podemos extender el número de operaciones posibles editando el código manualmente.
Para ver el código de los pasos aplicados abrimos el Editor Avanzado

En el recuadro rojo vemos el código del paso aplicado (#"Filas agrupadas"). Dentro la función Table.Group vemos las funciones List.Sum y List.Average. Fácilmente podemos entender que para crear la columna "Total Ventas" Power query aplica la función List.Sum que totaliza las ventas de cada país, como está definido por el parámetro {"País"} de la función Table.Group.

Ahora supongamos que queremos agregar dos columnas que muestren el primer y el último producto de cada país por orden de aparición en la lista . En la lista desplegable no aparecen las operaciones "Primer elemento de la lista" y "Último elemento de la lista" (existen Max y Min, pero estas funciones se aplican en columnas numéricas y nosotros queremos operar sobre una columna de texto, [Producto]).
Si investigamos las funciones disponibles para el tipo List veremos que existen List.First y List.Last

Vamos a agregar manualmente estas funciones imitando la sintaxis de las funciones usadas en la agrupación (comenzamos con { seguido por el nombre de la columna entre comillas, la expresión each, la función, el tipo de dato, y cerramos con un })

Apretamos "Listo" en el editor y...voila!

Supongamos ahora que nos piden crear un informe que muestre los dos productos con mayor volumen de ventas en cada país.
En la lista de operaciones tenemos Max que nos da un resultado, el producto con mayor volumen de ventas pero ninguna operación que nos de más de un valor por fila.
Nuevamente acudimos a la lista de funciones y vemos que, efectivamente, existe la función List.MaxN que vamos a utilizar para nuestra misión.

Si nos encontramos en la etapa 4 del aprendizaje del Power Query podemos escribir todo el código directamente en el editor avanzado. Pero si estamos todavía en alguna de las etapa anteriores o queremos ahorrarnos buena parte del tecleado haremos lo siguiente:

1 - Creamos la consulta y ordenamos la tabla en orden ascendente por [País] y descendente por [Ventas] (si bien podemos hacerlo en la tabla original, al hacerlo en la consulta nos aseguramos que siempre la tabla estará ordenada)

2 - Usamos Agrupar por - Básico para crear el código que luego modificaremos

2 - Abrimos el Editor Avanzado para ver el código

3 - Agregamos la función List.MaxN (que tiene dos argumentos: la columna donde debe operar [Ventas] y el número de resultados que debe dar)

Importante: no especificamos el tipo de dato.

Apretamos "Listo" y obtenemos esta consulta

En lugar de valores la columna "Mas vendidos" contiene "listas" (List). Cada List contiene los dos valores máximos de cada país. Podemos verlo haciendo un clic en algún espacio vacío a la derecha de la palabra List

Para poner los valores en la consulta vamos a expandir la columna haciendo un clic en la doble flecha a la derecha del encabezado de la columna

Como vemos, hay dos posibilidades:

• Expandir en nuevas filas: cada valor aparecerá en una nueva fila; los valores de las restantes columnas se duplicarán

• Extraer valores: los valores aparecerán en la columna con o sin un separador entre ellos

Si elegimos "Punto y coma" (o cualquier otro separador; en el caso de campo numérico no es recomendable usar el punto o la coma como separador) obtenemos una columna de tipo Texto con los valores separados por el carácter elegido

A partir de aquí podemos dividir la columna para obtener dos columnas: [Mas vendidos.1]  y [Mas vendidos.2]

Esta consulta resulta un tanto "huérfana". Le falta el nombre del producto para que convertirse en un informe realmente útil.
Para agregar el nombre de los dos primero productos con mayor volumen de venta en cada país vamos a agregar nuevamente la función List.FirstN (este es el motivo por el cual empezamos la consulta ordenando la tabla de mayor a menor según el campo [Ventas])

Como pueden ver hemos cambiado los encabezamientos de las columnas que vamos a agregar para darles nombres más significativo.
Tenemos ahora dos columnas que podemos expandir

Expandimos las columnas usando el delimitador "punto y coma" (o cualquier delimitador personalizado que no aparezca en los valores a separar, como # por ejemplo)

con este resultado

Usamos Dividir columna  para separar los nombres de los productos y su volumen de venta en cada fila

En este ejemplo he quitado la columna [Total Ventas], pero podríamos haberla dejado para calcular, por ejemplo, qué porcentaje representan cada uno de los productos del total vendido en el país.

En este post he mostrado como podemos extender las posibilidades de Agrupar por más allá de las ofrecidas en la interfaz del usuario. El último ejemplo tiene una debilidad; la cantidad de productos a mostrar por país esta "hard coded" (es una constante) en el código. Si queremos mostrar un número distinto de productos tendremos que editar la consulta e introducir el cambio manualmente.
En el próximo post veremos cómo podemos "dinamizar" este  modelo.