jueves, 20 de diciembre de 2018

Power Query: Ventas acumuladas condicionadas

Continuando la serie de artículos sobre Power Query (Obtener y transformar) de Excel, hoy toca hablar de como lograr un acumulado creciente condicionado, i.e., que se acumulen cantidades según se cumplan ciertas condiciones.

En la imagen siguiente se muestra cuál es el origen de nuestros datos (listado de ventas por año y país) y cuál la meta:

Power Query: Ventas acumuladas condicionadas



Veamos más claramente qué es lo que necesitamos...
Si nos fijamos en los movimientos de ventas para España (ES) tenemos:
2018 ES 3907
2018 ES 1000
2019 ES 3727
2020 ES 4123
2021 ES 1605

que si vamos sumando de manera incremental, comprobamos que nuestras ventas acumuladas para ES serán:
2018 ES 3907 3907
2018 ES 1000 4907
2019 ES 3727 8634
2020 ES 4123 12757
2021 ES 1605 14362

Esto mismo lo necesitamos para cada país...
Nótese que puede haber movimientos repetidos por país y año !!


Las funciones M de Power Query que se van a emplear son:
List.Sum(listado): que nos devuelve una suma a partir de los elementos del listado indicado.
Table.SelectRows(tabla_de_trabajo, condición as function)): Devuelve una tabla que contiene únicamente las filas que responden a la condición dada.


Comenzamos.
Primer paso cargar la tabla al Editor de consultas de Power Query.


Veamos, antes de detallar los pasos, el código resultante de nuestra consulta:
let
Source = Excel.CurrentWorkbook(){[Name="TblVentas"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Año", Int64.Type}, {"País", type text}, {"Ventas", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Índice", 1, 1),

RunTotal = Table.AddColumn(#"Added Index", "Ventas Acumuladas", each let País=[País],Año=[Año],Índice=[Índice] in
List.Sum(Table.SelectRows(#"Added Index", each [Índice]<=Índice and [País]=País and [Año]<=Año)[Ventas])), #"Removed Columns" = Table.RemoveColumns(RunTotal,{"Índice"}) in #"Removed Columns"


Power Query: Ventas acumuladas condicionadas



Se observa que el primer paso ha sido añadir una columna Indizada:
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Índice", 1, 1),

Desde el menú del Editor de consulta > Agregar columna > Columna de índice > Desde 1.
Esta columna es importante para el tratamiento de elementos repetidos por país y año... Si tuviéramos la certeza no estuvieran repetidos, podríamos obviar este paso.


Para el siguiente paso, el cálculo condicionado de las Ventas acumuladas, añadimos una columna personalizada con la siguiente fórmula:
RunTotal = Table.AddColumn(#"Added Index", "Ventas Acumuladas", each let País=[País],Año=[Año],Índice=[Índice] in
List.Sum(Table.SelectRows(#"Added Index", each [Índice]<=Índice and [País]=País and [Año]<=Año)[Ventas])),

Power Query: Ventas acumuladas condicionadas



Esta columna de cálculo realiza la selección de las filas que cumplen las condiciones pedidas (año y país) para sumar las [Ventas] de éstos.
Además se ha añadido la condición de índice para tener en cuenta los registros duplicados.


En el último paso he optado por eliminar la columna de Índice.

Tras finalizar, y Cerrar y Cargar tenemos nuestro resultado.... tal como veíamos en la primera imagen.

No hay comentarios:

Publicar un comentario

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