martes, 1 de noviembre de 2022

Power Query: Proyectar importes

Un ejercicio interesante para proyectar cantidades hacia el futuro, generando las fechas de la nada (o 'casi').
Imagina que tienes un listado de proveedores con unas condiciones pactadas de pago distribuidas a lo largo del año...
Power Query: Proyectar importes


Fíjate en la imagen como partimos de una tabla sin fechas, pero con una referencia temporal, que nos permite la distribución de los pagos a lo largo del año: campo 'Meses pago'.
Distribución o proyección que haremos en este caso para dos años {2022,2023}, suponiendo un reparto del 'Importe total' a partes iguales cada año!!.
Ejemplo, para un proveedor debemos repartir 100 eur/año en dos pagos anuales, los meses 2 y 5...
Por tanto las fechas del reparto para el 2022 y 2023 serían:
05/02/2022, 05/05/2022, 05/02/2023, 05/05/2023
e importes de 50 eur por fecha, esto es, 100 eur cada año

Veamos el código en lenguaje M, sabiendo que nuestra tabla se llama 'Tabla1':
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    //Creamos una lista de fechas ordenadas a partir del detalle
    //del campo 'Meses pago'
    //emplearemos List.TransformMany para ello
    AddListaFechas = Table.AddColumn(Origen, 
                                    "FechasPago",
                                    each List.Sort(List.TransformMany(
                                            Text.Split(Text.From([Meses pago]),"|")??[Meses pago],
                                            (año)=> {2022,2023},
                                            (mp,año)=> #date(año, Number.From(mp), 5)))),
    
    //Con List.Repeat generamos una Lista de importes,
    //uno por cada fecha.
    //Para luego unirlo en una tabla junto a las fechas anteriores
    AddImportesPagoUltimo = 
        Table.AddColumn(AddListaFechas, 
                    "ImportePago", 
                    each Table.FromRows(
                                List.Zip({[FechasPago],
                                        List.Repeat({[Importe total]/[Num pagos]},List.Count({2022,2023})*[Num pagos])}),{"Fecha","Importe"} ) ),

    ColumnasQuitadas = Table.RemoveColumns(AddImportesPagoUltimo,{"FechasPago"}),
    
    //Expandimos los datos de las tablas
    ExpandeFechas = Table.ExpandTableColumn(ColumnasQuitadas, "ImportePago", {"Fecha", "Importe"}, {"Fecha", "Importe"}),
    TiposCambiados = Table.TransformColumnTypes(ExpandeFechas,{{"Importe total", Currency.Type}, {"Fecha", type date}, {"Importe", Currency.Type}})

    /*
    //Si quisieramos verlo dinamizado...
    ColumnaDinamizada = Table.Pivot(
                    Table.TransformColumnTypes(TiposCambiados, {{"Fecha", type text}}, "es-ES"), 
                    List.Sort(List.Distinct(Table.TransformColumnTypes(TiposCambiados, {{"Fecha", type text}}, "es-ES")[Fecha])), 
                    "Fecha", 
                    "Importe")
*/
in
    TiposCambiados

El aspecto clave de este código, y lo que nos permite generar la secuencia de fechas, dada por los 'Meses de pago', tantas veces como años hayamos dispuesto, es el uso de la función List.TransformMany:
List.TransformMany(list as list, collectionTransform as function, resultTransform as function) as list

Esta es una de esas funciones 'complicadas' de interpretar, pero tremendamente potentes (como List.Generate o List.Accumulate entre otras).

¿Qué hace y cómo funciona List.TransformMany??
Trabaja a partir de dos series o listas... y recorre o aplica una transformación elemento a elemento de una lista sobre los elementos de la primera... ¿raro de explicar o entender?.
Pongamos un ejemplo simple...
Tenemos un listado de nombres y otro de apellidos... y queremos obtener las combinaciones posibles de los nombres con apellidos.
Por tanto recorreremos cada Apellido, uniéndolo con cada Nombre:
let
    Origen = List.TransformMany(
                    {"Romero","Sánchez","García"},
                    (nombre)=> {"Ismael", "Raquel"},
                    (apellido, nombre)=> nombre & " " & apellido) 
in
    Origen

El código toma el primer apellido de la lista: 'Romero' y aplica una acción empleando cada elemento de la segunda lista (los nombres')... por tanto tendríamos: Ismael Romero, Raquel Romero.
Seguidamente iríamos al segundo apellido de la lista y le volvería a aplicar los nombres de la segunda lista: Ismael Sánchez, Raquel Sánchez.
Concluiría el proceso con el tercer apellido, aplicando nuevamente los dos nombres: Ismael García, Raquel García.
Lo vemos en el resultado devuelto del ejemplo anterior:
Power Query: Proyectar importes


Para nuestro ejemplo de proyectar fechas, la función List.TransformMany nos va a permitir generar la misma secuencia de fechas (por los Meses de pago) para cada uno de los años dados en nuestra segunda lista {2022,2023}... tal como necesitamos.
Fíjate en la estructura base aplicada:
AddListaFechas = Table.AddColumn(Origen, "FechasPago", each List.Sort(List.TransformMany( Text.Split(Text.From([Meses pago]),"|")??[Meses pago], (año)=> {2022,2023}, (mp,año)=> #date(año, Number.From(mp), 5))))
Sobre todo en la parte 'profunda' donde usamos List.TransformMany:
List.TransformMany(
Text.Split(Text.From([Meses pago]),"|")??[Meses pago],
(año)=> {2022,2023},
(mp,año)=> #date(año, Number.From(mp), 5))


Impresionante función ;-)

Otro aspecto curioso es cómo unimos el resultado de dos listas en una tabla.
Una vez generadas las dos listas, empleamos List.Zip para tener pares de elementos 'unidos'...
Y finalmente, sobre esa lista de listas que nos devuelve List.Zip, aplicamos Table.FromRows para convertirla en una tabla y poder retornar el resultado deseado...

No hay comentarios:

Publicar un comentario

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