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

Ampliando las posibilidades de Agrupar en Power Query - segunda nota

En la nota anterior vimos un ejemplo de como ampliar las posibilidades de Agrupar por editando el código en el Editor Avanzado.
Al final del post señalé que el ejemplo tenía una debilidad notoria: la falta de dinamismo. La cantidad de productos a mostrar es una constante, de manera que si queremos mostrar una cantidad distinta de productos tendremos que editar el código y cambiar manualmente los valores donde sea necesario. Al hacerlo también nos exponemos a la posibilidad de introducir errores en el código.
En este post voy a mostrar como convertir el modelo de la nota anterior en un modelo dinámico y también como hacer cambios en el código generado automáticamente para evitar fallas o errores en la actualización de datos.

Abrimos la ventana del Editor Avanzado para ver el código de la consulta

1:  let  
2:    Origen = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],  
3:    #"Filas ordenadas" = Table.Sort(Origen,  
4:      {{"País", Order.Ascending},   
5:      {"Ventas", Order.Descending}}  
6:      ),  
7:    #"Filas agrupadas" = Table.Group(#"Filas ordenadas", {"País"}, {  
8:      {"Total Ventas", each List.Sum([Ventas]), type number},  
9:      {"Producto mas vendido", each List.FirstN([Producto],2)},  
10:      {"Total Producto", each List.MaxN([Ventas],2)}  
11:      }),  
12:    #"Valores extraídos" = Table.TransformColumns(#"Filas agrupadas",   
13:      {"Producto mas vendido",   
14:        each Text.Combine(List.Transform(_, Text.From), ";"),   
15:          type text}),  
16:    #"Valores extraídos1" = Table.TransformColumns(#"Valores extraídos",   
17:      {"Total Producto", each Text.Combine(List.Transform(_, Text.From), ";"),   
18:          type text}),  
19:    #"Dividir columna por delimitador" = Table.SplitColumn(#"Valores extraídos1",   
20:      "Producto mas vendido", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),   
21:        {"Producto mas vendido.1", "Producto mas vendido.2"}),  
22:    #"Dividir columna por delimitador1" = Table.SplitColumn(#"Dividir columna por delimitador",   
23:      "Total Producto", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),   
24:        {"Total Producto.1", "Total Producto.2"}),  
25:    #"Columnas quitadas" = Table.RemoveColumns(#"Dividir columna por delimitador1",{"Total Ventas"}),  
26:    #"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas quitadas",{{"Total Producto.2", type number}})  
27:  in  
28:    #"Tipo cambiado"  

Para que el código sea más legible he dividido los distintos pasos aplicados en varias líneas. Para hacerlo ubicamos el marcador en la posición requerida y apretamos Enter; usando la tecla Tab podemos separar la línea del margen.

Veamos ahora cómo convertir la consulta de la nota anterior en un modelo dinámico

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.