projection in power query moving average

[luis-fer-va]

Hola expertos,

Estoy trabajando en una proyección para estimar los valores de los próximos 6 meses. El proceso varía según el parámetro seleccionado. Por ejemplo, si la proyección es a 6 meses, el procedimiento sería el siguiente:

1. Tomar los últimos 6 registros reales.
2. Calcular su promedio.
3. Usar el resultado como nuevo valor en la siguiente fila.

Este proceso se repite incluyendo el valor recién proyectado como parte de los últimos 6 registros, calcular nuevamente el promedio y agregar el resultado como el siguiente valor proyectado, de manera iterativa.

Logré crear un ejemplo con lo que se requiere utilizando listas, el cual comparto abajo 🔽

let
  // Initial Data
  InitialData = {100, 200, 150, 300, 250, 400},

  //Numbers row to generate
  AdditionalRecords = 12,
 
  // generate projected values
  FullData = List.Generate(
        () => [Index = 0, ListSoFar = InitialData], // Estado inicial
        each [Index] < AdditionalRecords, // Condición para continuar
        each [
            Index = [Index] + 1,
            ListSoFar = List.Combine({[ListSoFar], {List.Average(List.LastN([ListSoFar], 6))}}) // Add new average
        ],
        each List.Average(List.LastN([ListSoFar], 6)) // Generate only new projected
    ),
  // Combine the initial values ​​with the generated values
  Result = List.Combine({InitialData, FullData}),
  #"Convert to table" = Table.FromValue(Result),
  #"Transform columns" = Table.TransformColumnTypes(#"Convert to table", {{"Value", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Value", null}})
in
  #"Replace errors"

Este es el resultado del codigo anterior.

Sin embargo, no sé cómo implementar lo mismo con mis datos reales, que además del valor incluyen otras columnas. ¿Alguien podría orientarme sobre cómo adaptarlo a un conjunto de datos más complejo? ¡Gracias de antemano!

• Las letras en verde representan los valores generados; en la columna "value", los registros en verde son los valores proyectados y corresponden al resultado esperado.

• Los valores en azul oscuro son los valores iniciales utilizados para calcular el promedio.

Muchas gracias. Quedo atento a sus respuestas.

Atentamente,
Luis Vallejo.

[luis-fer-va]

Muchas gracias @Anonymous por tu ayuda, era justo lo que estaba buscando.

[Anonymous · Accepted solution]

Hi @luis-fer-va 

You can refer to the following code in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc49CoAwDAXgu2Tu0Nj/W4hr6SDSRQRBvD/2LZJAhwz5yAuvVuJMhnjM1veLmqmUIYuUAHFSPMSrlB1bkJLwI0pxSCQpDMlSCv4UJWjHVsVQhlVrjwNWtQE4XJ/77Mf7m59YmFicWJpYltY+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Index = _t, Mark_Real_Project = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=Table.SelectRows(#"Changed Type",each [Mark_Real_Project]="Real"),
b=List.Max(a[Index]),
c=List.LastN(a[Value],6),
d=Table.RowCount(#"Changed Type"),
e=List.Generate(
    ()=>[x=0,y=c],
    each [x]<=d,
    each [y=List.Combine({[y],{List.Average(List.LastN([y],6))}}),x=[x]+1],
    each List.Average(List.LastN([y],6))
),
f= if [Mark_Real_Project]="Real" then [Value] else e{[Index]-b-1}
in f)
in
    #"Added Custom"

Ouptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.