martes, 18 de diciembre de 2018

Power Query: Saldo acumulado creciente

Aprenderemos hoy una manera muy simple, con Power Query (Obtener y transformar), de aplicar una columna de saldo acumulado (running total) a una tabla de movimientos bancarios...

Power Query: Saldo acumulado creciente



Para este caso emplearemos algunas funciones M de Power Query como:
Table.ReplaceValue(tabla, oldValue as any, newValue as any,replacer as function, columnsToSearch as {Text}): nos permite reemplazar en columnas concretas de la tabla indicada el viejo valor (oldValue) por el nuevo (newValue), empleando para ello funciones de reemplazamiento específicas, tales como text.Replace o Value.Replace.
Table.AddIndexColumn(tabla, newColumnName as text, optional initialValue as nullable number, optional increment as nullable number): insertando una nueva columna indizando los registros de nuestra tabla.
List.Sum(listado): que nos devuelve una suma a partir de los elementos del listado indicado.
List.Range(listado, offset as number, optional count as number): devuelve un conteo de elementos comenzando desde el valor indicado con 'offset'.

Si bien, muchas de ellas, las podemos emplear directamente usando los asistentes del editor de consultas.


Veamos nuestra consulta completa:
let
Source = Excel.CurrentWorkbook(){[Name="TblMovimientos"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fechas", type date}, {"Concepto", type text}, {"Debe", Int64.Type}, {"Haber", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Debe","Haber"}),

Saldo = Table.AddColumn(#"Replaced Value", "Saldo1", each [Debe]-[Haber]),
Indice = Table.AddIndexColumn(Saldo, "Índice", 1, 1),
SaldoAcumulado = Table.AddColumn(Indice, "Saldo Acumulado", each List.Sum(List.Range(Indice[Saldo1],0,[Índice]))),
#"Removed Columns" = Table.RemoveColumns(SaldoAcumulado,{"Saldo1", "Índice"})

in
#"Removed Columns"

Power Query: Saldo acumulado creciente



Nuestra trabajo comienza cargando la tabla de datos de nuestra hoja de cálculo al Editor de consultas de Power Query.
Una vez cargado, lo primero que haremos será reemplazar los valores 'null' por ceros, y así poder operar sobre los importes cargados y abonados correspondientes a nuestros movimientos bancarios.
Así pues añadimos un nuevo paso con la función Table.ReplaceValue
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Debe","Haber"}),

donde reemplazamos el valor viejo 'null' por el nuevo '0', en la columnas "Debe" y "Haber".


Nota: para añadir nuevos pasos puedes acceder al Editor avanzado; o bien paso a paso desde el panel de 'Configuración' en la sección de Pasos aplicados, haciendo clic derecho > Insertar paso después.

Una vez reemplazados los valores no válidos (null) añadiremos una columna personalizada como la resta del campo [Debe] menos [Haber]:
Saldo = Table.AddColumn(#"Replaced Value", "Saldo1", each [Debe]-[Haber]),

al campo le he llamado 'Saldo1'.... aunque este campo, igual que el siguiente, los eliminaremos al final de la consulta.
Este campo lo insertamos para que nos facilite el trabajo de lograr nuestra columna de acumulado creciente (running total).


Siguiente paso, añadimos una columna indizada que comience por 1 y se vaya incrementando de 1 en 1:
Indice = Table.AddIndexColumn(Saldo, "Índice", 1, 1),

Esta columna es fundamental para lograr nuestro acumulado...


Último paso relevante. Añadimos una nueva columna con el cálculo necesario para lograr nuestro acumulado... empleando las funciones List.Sum y List.Range:
SaldoAcumulado = Table.AddColumn(Indice, "Saldo Acumulado", each List.Sum(List.Range(Indice[Saldo1],0,[Índice]))),

esto nos permite acumular desde el primer dato o registro del campo [Saldo1], registro a registro... tal como queríamos.


Si lo deseas puedes limpiar la tabla resultante eliminando columnas no necesarias (Saldo1 e Índice):
#"Removed Columns" = Table.RemoveColumns(SaldoAcumulado,{"Saldo1", "Índice"})


Como se observaba en la primera imagen, el resultado es el requerido... podemos ver una columna con el Saldo acumulado de nuestros movimientos.

6 comentarios:

  1. Todas las publicaciones sobre lenguaje M son de agradecer. Muchas gracias por el post

    ResponderEliminar
  2. Hola Ismael,

    Con todo el cariño y respeto, tu fórmula para llevar un acumulado(running total)en PQ con sumas parciales puede llegar a ser muy muy lenta si manejas tablas con muchos registros. Lo sé porque lo he sufrido en primera persona ;-)

    Te propongo una alternativa. La "magia" está en la linea que define una nueva lista llamada "Output".

    Lo que hace es transformar un rango del 0 al número de elementos de la lista de origen (menos uno) en el acumulado de la lista de origen. Toda la gracia está en la arroba (@) que te permite "salir" del contexto de la función y recuperar el valor de la propia lista que se está generando.

    "List.Buffer" es un truco para acelerar (a veces) PowerQuery. "Table.Buffer" también existe, por cierto.

    Un cordial saludo,

    let
    Origen = Excel.CurrentWorkbook(){[Name="TablaMov"]}[Content],
    ConSaldo = Table.AddColumn(Origen, "Saldo", each (if [Debe] = "" then 0 else [Debe]) - (if [Haber] = "" then 0 else [Haber])),
    ConIndice = Table.AddIndexColumn(ConSaldo,"TempIndex"),

    Input = ConIndice[Saldo],

    Output = List.Buffer(List.Transform({0..List.Count(Input)-1}, each Input{_} + (if _ = 0 then 0 else @Output{_-1}))),

    ConAcumulado = Table.AddColumn(ConIndice, "Acumulado", each Output{[TempIndex]}),
    Salida = Table.RemoveColumns(ConAcumulado, "TempIndex")
    in
    Salida

    ResponderEliminar
    Respuestas
    1. Muchas gracias Daniel
      se agradece el comentario y la lección.

      Soy consciente de las limitaciones de proceso, y en general, nos encontramos con estos problemas empleando PQ..

      Desconocía el uso de List.Buffer, pero sin duda, agiliza algo el cálculo

      De nuevo muchas gracias por el aporte... me encanta aprender cada día algo nuevo :)

      Saludos

      Eliminar
  3. De nada Ismael, gracias a ti.

    He reducido el código a la mínimna expresión, hace lo mismo y quedaría así:

    let

    Origen = Excel.CurrentWorkbook(){[Name="TablaMov"]}[Content],

    ConSaldo = Table.AddColumn(Origen, "Saldo", each (if [Debe] = "" then 0 else [Debe]) - (if [Haber] = "" then 0 else [Haber])),

    Salida = Table.Buffer(Table.AddColumn(ConSaldo, "Acumulado", each [Saldo] + (try @Salida[Acumulado]{List.PositionOf(ConSaldo[Saldo],[Saldo])-1} otherwise 0)))

    in

    Salida


    De esta forma se podría hacer desde el interfaz gráfico añadiendo una columna personalizada con la siguiente fórmula:

    each [Saldo] + (try @Salida[Acumulado]{List.PositionOf(ConSaldo[Saldo],[Saldo])-1} otherwise 0)

    Qué hace, pues más o menos lo que haríamos usando referencias de celdas de Excel. Crea una nueva columna "Acumulado" con el valor del campo [Saldo] de cada registro más el valor del campo [Acumulado] del registro anterior.

    "try ... othewise" es similar a la fórmula SI.ERROR() de Excel para evitar un error en el primer registro.

    Finalmente, List.PositionOf devuelve el índice de la fila. Si le restas uno tienes el índice del registro anterior, al que accedes con la arroba.

    Que yo sepa, no hay maneras sencillas de construir un acumulado en PQ, hay cosas peores con List.Generate, etc.

    ResponderEliminar

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