martes, 7 de junio de 2022

Power Query: Diferencia dinámica entre columnas

Días atrás se me planteó si habría alguna posibilidad, empleando Power Query, de automatizar el cálculo entre las distitnas columnas de unas tablas dadas, a partir de otros listados personalizados de columnas con las que operar.
Power Query: Diferencia dinámica entre columnas


Tenía los datos, tenía las reglas a seguir.. asi que empecé el análisis.

En un primer paso, obviamente, realicé la carga de las tablas implicadas:
-Dos con los datos sobre los que operar llamadas: dData1 y dData2
-Otras dos con la lista de campos a restar uno-a-uno: lList1 y lList2

El segundo paso ya dentro del Editor de Power Query consiste en unir fila por fila las dos tablas principales dData1 y dData2, por lo que opté por la forma clásica:
1-añadir una columna de índice en cada tabla,
2-para luego realizar una combinación fila a fila entre ambas...
El código generado con el asistente quedó entonces:
let
    //OJO!, en la carga de las tablas dData1 y dData2 se ha añadido una columna Índice !!
    Origen = Table.NestedJoin(dData1, {"Índice"}, dData2, {"Índice"}, "dData2", JoinKind.LeftOuter),
    Se_expandio_dData2 = Table.ExpandTableColumn(Origen, "dData2", {"A2", "B2", "C2"}, {"A2", "B2", "C2"}),
    Columnas_quitadas = Table.RemoveColumns(Se_expandio_dData2,{"Índice"})
in
    Columnas_quitadas

Power Query: Diferencia dinámica entre columnas

OJO!!. recuerda que al momento de cargar las dos tablas de datos (dData1 y dData2) se le han agregado sendas columnas de Índice!!

En el tercer paso preparamos un listado único, a partir de las dos listas de columnas (lList1 y lList2), con la que tendremos un listado de restas entre columnas...
let
    //empleamos la función List.Zip para entretejer los elementos de ambos listados
    Origen = List.Zip({lList2[List2],lList1[List1]}),

    //para acabar montando una cadena de texto que 'simule' una operación entre columnas
    Unido=List.Transform(Origen, each "[" & Text.Combine( _  ,"]-[") & "]" )
in
    Unido

Empleamos List.Zip para cruzar elemento a elemento los datos de ambas listas...
Obteniendo una Lista de listas, sobre la que trabajamos para resultar una cadena de texto (con Text.Combine) que se asemeje a lo que escribiriamos si trabajaramos entre columnas...
Power Query: Diferencia dinámica entre columnas


Ya tenemos los datos unidos de mabas tablas, y tenemos los cruces entre columnas para realizar ese cálculo de la diferencia/resta.
Último paso. Emplearemos la función List.Accumulate para agregar de forma dinámica tantas nuevas columnas como surjan de la combinación de las tablas lList1 y lList2 (puedes ver un ejemplo similar en este artículo).
let
    Origen = #"dData1+dDdata2",
    //https://blog.excelforo.com/2021/11/power-query-operar-sobre-columnas.html
    //List.Accumulate(list as list, seed as any, accumulator as function) as any
    //Acumula un valor de resumen de los elementos de la lista list, mediante accumulator. 
    //Se puede establecer un parámetro de inicialización opcional, seed.
    AddCols =  List.Accumulate(
                List_Dif,
                Origen,
                (tabla_actual, nueva_col) =>
                    if List.Contains(Table.ColumnNames(tabla_actual), nueva_col) then
                        tabla_actual
                    else 
                        Table.AddColumn(tabla_actual, 
                                        nueva_col, 
                                        each Expression.Evaluate(nueva_col,[_ = _]))  )
in
    AddCols

Con List.Accumulate realizamos un recorrido por la recien generada Lista de diferencias entre columnas:
[A2]-[A1]
[C1]-[B1]
[B2]-[C2]

verificando con el condicional if...then..else... si la columna a agregar ya existiera, en caso negativo, empleamos Table.AddColumn para dicha acción.
Lo interesante es qué valor añadimos para cada fila de esa nueva columna... donde aprovechándonos del elemento construido (recuerdas el que simulaba una resta entre columnas?) y aplicando Expressión.Evaluate, indicando un entorno de trabajo controlado bajo las condiciones del bucle ([_ = _] y no #shared puesto que son situaciones virtuales momentáneas...

El resultado final es, por tanto, el deseado una vez cargada y cerrada la consulta y devuelta a la hoja de cálculo:
Power Query: Diferencia dinámica entre columnas

No hay comentarios:

Publicar un comentario

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