Totales acumulados con Power Query

No me considero un profesional experto en Excel y aledaños (Power Query, PowerPivot), pero si un usuario experimentado. Por ese motivo las notas de este blog reflejan más mi trabajo cotidiano con esta fantástica herramienta que un enfoque técnico-profesional.

Una vez dicho ésto, vayamos al punto de la nota: en algún momento de nuestra vida tuvimos o tendremos que calcular totales o saldos acumulados ("running totals" en inglés).

En Excel Clásico (Excel Clásico = Excel sin Power Query y PowerPivot) sabemos hacerlo con fórmulas o, mejor aún, con tablas dinámicas. ¿Por qué, entonces, hacerlo con Power Query? Porque puede ser parte de una serie de transformaciones que estamos haciendo dentro de la consulta o porque, sencillamente, nos resulte más eficiente.

Sea cual sea el motivo vamos a ver cómo hacerlo en el entorno del Power Query.

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.

Otra forma de comparar tablas con Power Query

En la nota anterior sobre el tema mostramos como comparar tablas con Power Query para encontrar, por ejemplo, diferencias entre ambas tablas. Por ejemplo, podemos comparar dos listas de nombres para controlar que nombres de las lista1 no aparecen en la lista2 o viceversa.

Todas las tareas las hicimos usando la interfaz del usuario. Es decir, sin necesidad de escribir o modificar ninguna línea de código para obtener el resultado. Sin embargo con cada paso que aplicamos, y que vemos reflejado en el panel de "Pasos Aplicados", Power Query está escribiendo líneas de código en su lenguaje, conocido como el lenguaje "M".

Voy a aprovechar esta nota para mostrar como podría comparar dos listas un usuario con ciertos conocimientos del lenguaje. Para lo cual voy a dedicar algunas líneas al lenguaje M.

El lenguaje M comprende objetos y funciones. Entre los objetos voy a mencionar las Tablas y las Listas. La diferencia entre una tabla y una lista, dicho en forma general, es que la lista siempre tendrá una sola columna. Así, por ejemplo, podemos tomar una columna de una tabla y convertirla en Lista y, a su vez, podemos convertir una lista en Tabla.
El motivo para estas transformaciones es que cada objeto, Listas y Tablas en nuestro caso, tiene su propia colección de funciones.
Power Query no tiene incorporado  un asistente de funciones de manera que para consultar qué funciones existen tenemos que abrir está página (por ahora no se ha publicado una versión en castellano).

En nuestro ejemplo vamos a usar la función List.Difference. Las funciones están organizadas por categorías; la nuestra se encuentra en la categoría List Functions





Como vemos la función utiliza dos variables, las listas a comparar, y una tercera opcional que ignoraremos en esta nota.

"List" que antecede al nombre de la función nos indica que se trata de una que actúa sobre Listas. Esto quiere decir que para usarla tendremos que convertir, previamente, las columnas de nuestras tablas a listas.

Para evitar un tsunami de palabras en un largo post, voy a mostrar y explicar el proceso con este video



Ahora bien, si podemos hacer la comparación usando la interfaz de usuario, ¿por qué hacerlo escribiendo código?
La interfaz de usuario de Power Query es muy poderosa; nos permitirá resolver algo así como el 40% de nuestras necesidades de transformación de datos. Pero no todos los casos se pueden resolver sin escribir código o de la manera más eficiente.

Según Gil Raviv estas son las etapas del aprendizaje de Power Query y el lenguaje M

1. Solo interfaz - nos permite solucionar aproximadamente el 40% de los problemas.
2. Edición básica en la barra de fórmulas - Si bien no tenemos sólidos conocimientos de M y las funciones, si podemos asociar elementos en las fórmulas con las pasos dados con la interfaz y lograr ciertas transformaciones. En esta etapa ya podemos resolver el 60% de los problemas.
3. M en columnas personalizadas - En esta etapa dominamos el uso de columnas personalizadas y podemos crear fórmulas efectivas; dominamos el uso de condicionales y los operadores Booleanos. En esta etapa ya nos enfrentamos con éxito al 80% de los problemas.
4. Funciones personalizadas - En esta etapa ya dominamos el uso de funciones personalizadas lo que nos permite reusar transformaciones que hemos creado. Ahora ya podemos resolver el 95% de los problemas.
5. Iteraciones avanzadas - En esta etapa ya sabemos enfrentarnos a escenarios complejos y es cuando empezamos a usar funciones como List.Accumulate y List.Generate para crear iteraciones de transformaciones. Ya sabemos resolver el 99% de los desafíos que nos presenten; el 1% restante pueden ser resueltos usando otras herramientas.

Gil describe una sexta etapa sobre la cual no me voy a extender ya que está reservada a los super-humanos (o Cyborgs o seres de otras galaxias).
Para los simples mortales un buen punto de comienzo es el curso de Ivan Pinar Dominguez