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



Antes de empezar a modificar el código veamos el primer paso aplicado, #"Filas ordenadas" (líneas 2 a 6). Si nos fijamos por un momento en el editor de Power Query vemos números que aparecen a la izquierda del icono de ordenación nos indican en que secuencia se aplicó la transformación.




El método de ordenación de datos de Power Query es distinto al de Excel Clásico. En Power Query la segunda operación no altera el orden de la primera; en nuestro caso el orden de los países no se altera al ordenar en segunda instancia las ventas. En Excel si ordenamos la columna Ventas "desordenamos" la columna País.

Vamos a fijarnos ahora en el paso #"Filas agrupadas" (líneas 7 a 11).
En las líneas 9 ay 10 vemos las funciones List.FirstN y List.MaxN  cuyo segundo parámetro, cuando elementos de la lista extraer, es la constante 2.

Para que nuestro modelo sea dinámico necesitamos aquí una variable en lugar de una constante.

Creamos nuestra variable asignándola a una celda en una hoja del cuaderno Excel y creando un nombre definido que se refiera a esa celda (ya he mostrado este proceso en varias notas como en esta sobre como crear una tabla de amortización de préstamo).
Para crear la variable Productos_a_mostrar creamos un nombre definido homónimo

creamos una consulta conectada a esa celda

hacemos un clic izquierdo en la columna para abrir el menú contextual y aplicamos un "drill down" (espantosamente traducido a Rastrear desagrupando datos)

ahora veremos en el editor la ventana Herramientas de números

Activamos al pestaña Inicio y guardamos la consulta cono "solo conexión". En el panel de las consultas y conexiones del cuaderno veremos la consulta a la tabla de ventas y la variable que acabamos de crear

Modificamos las líneas 9 y 10 de la consulta reemplazando la constante 2 por el nombre de la variable Productos_a_mostrar

Para probar nuestra consulta vamos a cambiar el valor de la variable Productos_a_mostrar de 2 a 3 (cambiando el valor de la celda G1 en la hoja Ventas en nuestro ejemplo. Actualizamos al consulta y vemos que el informe en la hoja sigue mostrando sólo dos productos.
Para investigar dónde está el problema abrimos la consulta en el editor y vamos al paso Valores Extraídos1 y vemos que efectivamente cada columna contiene 3 valores separados por punto y coma

Sin embargo al dividir la columna por delimitador el resulta es dos columna para Producto más vendido y dos columnas para Total Producto.
Si nos fijamos en los pasos aplicados #Dividir columna por delimitador" y "#Dividir columna por delimitador1" vemos que las columna resultantes están codificadas (hard coded)

La solución es muy sencilla: eliminamos las porciones de códigos resaltadas en amarillo (técnicamente, la lista que contiene los nombres de las columnas a agregar)

Al hacerlo obtenemos el número de columnas deseado

Nos queda todavía una transformación más a realizar. Si nos fijamos veremos que las columnas [Total Producto.] están definidas como texto. Al cargar la consulta a la hoja de Excel estas columnas aparecerán como texto, no numéricas. Para evitar ésto, aplicamos un último paso seleccionando las columnas y convirtiendo el tipo a Número decimal. Aquí se nos presenta un nuevo desafío. El código creado (ya que hemos usado la interfaz de usuario para cambiar el tipo de dato) codifica las columnas a cambiar por su nombre

Si volvemos a cambiar el valor de la variable Producos_a_mostrar, al actualizar la consulta aparecerá un error.
La solución para "dinamizar" esta parte del código no es tan sencilla como en el paso anterior, motivo por el cual no voy a entrar en detalles técnicos. En el paso #"Tipo cambiado" reemplazamos la función Table.TransformColumnTypes por ésta

 #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna por delimitador1",List.Transform(  
             List.LastN(  
               Table.ColumnNames(#"Dividir columna por delimitador1"),Productos_a_mostrar),   
               each {_, type number})  
             ),  

Explicación breve. Usamos varias funciones "anidadas". De "adentro hacia afuera":

Table.ColumnNames da como resultado los nombres de la columna de la tabla (identificada con el nombre de paso anterior: #"Dividir columna or delimitador1")
List.LastN toma el resultado de Table.ColumnNames y crea una lista con los últimos N elementos de la lista. Por ese motivo usamos la variable Productos_a_mostrar como segundo argumento de la función.
List.Transform aplica la transformación a cada elemento de la lista anterior.

Ahora nuestro modelo es totalmente dinámico. Todo lo que tenemos que hacer es cambiar el valor de la variable en la hoja Ventas (en nuestro ejemplo) y actualizar la consulta.

Este el el código modificado completo

 let  
   Origen = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],  
   #"Filas ordenadas" = Table.Sort(Origen,  
     {{"País", Order.Ascending},   
     {"Ventas", Order.Descending}}  
     ),  
   #"Filas agrupadas" = Table.Group(#"Filas ordenadas", {"País"}, {  
     {"Total Ventas", each List.Sum([Ventas]), type number},  
     {"Producto mas vendido", each List.FirstN([Producto],Productos_a_mostrar)},  
     {"Total Producto", each List.MaxN([Ventas],Productos_a_mostrar)}  
     }),  
   #"Valores extraídos" = Table.TransformColumns(#"Filas agrupadas",   
     {"Producto mas vendido",   
       each Text.Combine(List.Transform(_, Text.From), ";"),   
         type text}),  
   #"Valores extraídos1" = Table.TransformColumns(#"Valores extraídos",   
     {"Total Producto", each Text.Combine(List.Transform(_, Text.From), ";"),   
         type text}),  
   #"Dividir columna por delimitador" = Table.SplitColumn(#"Valores extraídos1",   
     "Producto mas vendido", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),  
   #"Dividir columna por delimitador1" = Table.SplitColumn(#"Dividir columna por delimitador",   
     "Total Producto", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),  
   #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna por delimitador1",List.Transform(  
             List.LastN(  
               Table.ColumnNames(#"Dividir columna por delimitador1"),Productos_a_mostrar),   
               each {_, type number})  
             ),  
   #"Columnas quitadas" = Table.RemoveColumns(#"Tipo cambiado",{"Total Ventas"})  
 in  
   #"Columnas quitadas"