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.
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:
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...
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...
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).
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:
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
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...
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:
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
