martes, 16 de noviembre de 2021

Power Query: Operar sobre columnas variables

Un ejercicio interesante con Power Query es facilitar el cálculo sobre un número de columnas variables, sujetas a la elección del usuario, de forma dinámica.
Power Query: Operar sobre columnas variables

Se trata pues de permitir que el usuario elija una lista de columnas (meses en mi ejemplo) sobre las que operar (celdas P2:P5), que además controlemos que la selección coincida con los nombres de las columnas, para evitar fallos, y finalmente se realice la suma acumulada de los meses resultantes...

Para ello cargaremos las dos tablas (la de datos y la de la selección de meses) en el editor de Power Query; comenzaremos editando y convirtiendo en una Lista la selección de meses:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblMESES"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Meses a operar", type text}})[Meses a operar]
in
    TipoCambiado


Seguidamente editaremos la tabla de datos (que he llamado 'TblDATOS') para añadir las siguientes líneas de código:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"País", type text}, {"ene", Int64.Type}, {"feb", Int64.Type}, {"mar", Int64.Type}, {"abr", Int64.Type}, {"may", Int64.Type}, {"jun", Int64.Type}, {"jul", Int64.Type}, {"ago", Int64.Type}, {"sep", Int64.Type}, {"oct", Int64.Type}, {"nov", Int64.Type}, {"dic", Int64.Type}}),
    
    //Trabajamos únicamente con los campos que sí existen en nuestra tabla
    listaCampos=List.Intersect({Table.ColumnNames(TipoCambiado),TblMESES}),
    
    //OPCIÓN 1 -- Trabajando con Records
    AddCol_Operacion1 = Table.AddColumn(TipoCambiado, "Suma_1", each List.Sum(Record.ToList(Record.SelectFields(_, listaCampos))), Int64.Type),

    //OPCIÓN 2 -- Trabajando con Evaluate
    TransfMeses = "{" & Text.Combine(List.Transform(listaCampos, each "[" & _ & "]"),", ") & "}",
    AddCol_Operacion2 = Table.AddColumn(AddCol_Operacion1, "Suma_2", each List.Sum( Expression.Evaluate(TransfMeses, [_=_]) ), Int64.Type )
in
    AddCol_Operacion2


IMPORTANTE la línea donde verificamos la existencia de campos indicados en la selección de meses en la tabla de datos:
listaCampos=List.Intersect({Table.ColumnNames(TipoCambiado),TblMESES}),
donde generamos una lista 'nueva' solo con los campos existentes.. esto es, de la lista de meses propuesta en las celda P2:P5:
Enero, feb, mar, sep
comprobamos que sólo feb, mar, sep coinciden con los nombres de columnas de la TblDATOS.

Debemos saber a qué composición debemos llegar para alimentar la función List.Sum. Si hicieramos la acción manualmente veríamos lo siguiente:
= Table.AddColumn(AddCol_Operacion2, "Adición", each List.Sum({[feb], [mar], [sep]}), Int64.Type)
donde comprobamos que List.Sum necesita una lista 'especial' con los nombres de las columnas entre corchetes...
:OO
Esto es, necesitamos crear una lista algo diferente... una lista de columnas.

A continuación trabajamos con dos alternativas para dinamicar el cálculo (Suma en nuestro ejemplo) sobre las columnas anteriores.

En la primera opción generamos la lista de campos que requiere List.Sum empleando la selección de campos de un registro, ya que Record.SelectFields solo necesita una lista simple de columnas...
El resultado anterior, un conjunto de los valores de los campos seleccionados, lo convertimos a lista (Record.ToList), con las características requeridas por List.Sum.

El segundo método descrito es más indirecto, ya que trabajamos sobre literales de los nombres de las columnas... lo que nos exige emplear la función Expression.Evaluate para hacer entender que los textos generados sean interpretado como columnas ciertas.
Así pues generamos dinámicamente una cadena de texto que tenga la forma: '{[feb], [mar], [sep]}'
Esto se consigue en:
TransfMeses = "{" & Text.Combine(List.Transform(listaCampos, each "[" & _ & "]"),", ") & "}",
basicamente, con List.Transform recorremos cada elemento de la lista de meses: feb, mar, sep, concatenado cada uno con los corchetes, quedando una lista: [feb], [mar], [sep].
Lista que combinamos en un solo elemento con Text.Combine, para finalmente darle aspecto de lista incorporando las llaves al principio y fin de nuestra cadena:
"{" & [feb], [mar], [sep] & "}"
pero ojo!!, esto sigue siendo simplemente una cadena de texto!!!.
Para aportarle la funcionalidad que queremos, aplicaremos en su momento, la función Expression.Evaluate:
AddCol_Operacion2 = Table.AddColumn(AddCol_Operacion1, "Suma_2", each List.Sum( Expression.Evaluate(TransfMeses, [_=_]) ), Int64.Type )
Consiguiendo nuestro objetivo... tener una lista de columnas!!

Y finalmente, una suma de dichas columnas ;-)

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.