Determinar posición por grupos (Ranking) con Power Query

Esta nota viene a colación de una consulta sobre cómo calcular posiciones (ranking) por grupos con Power Query, tema sobre el cual publiqué en el pasado este post mostrando como hacerlo con Excel.

La consulta original era, dada una serie de datos agrupados por grupos, cómo calcular la posición (ranking) de cada elemento dentro del grupo usando Power Query.

Consideremos esta tabla de datos

Nuestra tarea es ordenar por volumen de ventas y de mayor a menor, las ventas mensuales en cada área.

Como toda tarea a realizar con Power Query empezamos por crear una consulta

Aplicamos dos pasos:
1 - cambiamos los tipos de datos
2 - rellenamos la columna [Area] hacia abajo

Voy a mostrar varias formas de hacer esta tarea por lo cual guardamos esta consulta como Solo conexión. Las distintas versiones las creamos a partir de esta consulta usando Referencia.

¡Manos a la obra!

Versión 1.

Creamos una consulta usando Referencia y ordenamos la columna [Area] de menor a mayor y la columna [Ventas] de mayor a menor. Recordemos que a diferencia de Excel clásico, el orden varias columnas es jerárquico, decir, el segundo orden no altera el primero.

Agrupamos la tabla por [Area] con un único campo [Datos] usando la operación Todas la filas

Si alguna vez se preguntaron preguntaron para qué sirve esta función, pues he aquí un ejemplo.
En el próximo paso agregamos una columna personalizada con la función

Table.AddIndexColumn([Datos],"Ranking",1,1)

Nuestra consulta se ve ahora así:

Eliminamos las columnas [Area] y [Datos] y expandimos la columna [Custom].
¡Voila!

Sólo nos queda descargar la consulta a una hoja de Excel.

Si queremos facilitar la lectura de la tabla podemos eliminar el nombre del área en todas las filas excepto la primera de cada grupo. Podemos hacerlo agregando los siguientes pasos:

1 - una columna personalizada con esta fórmula condicional:

Table.AddColumn(Source, "Custom", each if [Ranking]<>1 then null else [Area])

2 - eliminamos la columna [Area];

3 - movemos la columna recién creada al lado izquierdo de la tabla y cambiamos el nombre a Area

Cerramos y descargamos a una hoja

¿Conocen ese tipo de gente que siempre le encuentran un detalle a cada cosa? Por lo general son administrativos de contabilidad (aclaración: no tengo nada en contra de los contables; es más, muchos de mis mejores amigos son contables 😉). Pues bien, alguien ya me comentaba que la tabla ordenada no guardaba el orden original de las áreas (Norte-Sur-Este-Oeste).

Para hacer el ejercicio guardando el orden original de las áreas agregamos unos pasos al inicio de la consulta. Como ants. agrupamos la tabla por [Area] y creamos un campo con la operación Todas las filas. Luego agregamos una columna personalizada [Indice]

Esta columna nos va servir para volver la tabla final al orden original de las áreas.
Nótese que en la función Table.AddIndex nos referimos (el primer elemento de la función) a la tabla creada en el paso anterior (#"Grouped Rows") y no a la columna [filas] como hicimos en el ejercicio anterior (la columna [Datos]).

Ahora quitamos la columna [Area] y expandemos la tabla filas

A partir de aquí aplicamos los mismos pasos como en la consulta anterior con la diferencia que al ordenar la tabla usamos las columna [Ventas] e [Index] en lugar de [Area] e [Index]. Luego seguimos los pasos de la consulta anterior.