Optimizar codigo power query promedio movil

[luis-fer-va]

Buenas tardes, comunidad de Power BI.

Estoy enfrentando un desafío al trabajar con un código en Power Query que procesa aproximadamente 1 millón de filas en un Dataflow de Power BI Service. Al intentar actualizar el Dataflow, el proceso no logra completarse, probablemente debido al volumen de datos y la complejidad del código.

El objetivo principal del código es calcular un promedio móvil dinámico basado en los últimos "n" meses de datos reales, y funciona correctamente a nivel lógico. Sin embargo, el rendimiento es un problema crítico, y me gustaría saber si existe alguna manera de optimizarlo para que la actualización sea más eficiente.

Comparto el código completo a continuación:

let
  // Tabla de entrada
  Source = Paso1_Agrupación_Índice_Reiniciable_FiltroReales,
  #"Filtered rows <> estacionalidad" = Table.SelectRows(Source, each ([Base Proyeccion] = "Promedio")),
  // Agrupar por Pk_EmpresaCuentaTercero
  GroupedTable = Table.Group(
        #"Filtered rows <> estacionalidad", 
        {"Pk_EmpresaCuentaTercero","Escenario_ID"}, 
        {
            {"Data", each
                let
                    a = Table.SelectRows(_, each [Mark_Real_Proyeccion] = "Real"), 
                    b = List.Max(a[Index]), 
                     // Obtener el número de meses desde la columna de cada fila
                    Numero_de_Meses = a{0}[Numero de Meses], 
                    c = List.LastN(a[Importe], Numero_de_Meses), 
                    d = Table.RowCount(_), 
                    e = List.Generate(
                        () => [x = 0, y = c], 
                        each [x] <= d, 
                        each [y = List.Combine({[y], {List.Average(List.LastN([y], Numero_de_Meses))}}), x = [x] + 1], 
                        each List.Average(List.LastN([y], Numero_de_Meses))
                    ), 
                    UpdatedData = Table.AddColumn(
                        _, 
                        "RollingAverage", 
                        each if [Mark_Real_Proyeccion] = "Real" then [Importe] else e{[Index] - b - 1}
                    )
                in
                    UpdatedData
            }
        }
    ),
  #"Removed other columns" = Table.SelectColumns(GroupedTable, {"Data"}),
  // Expandir las subtablas para volver a una sola tabla
  #"ExpandedTable Calculo Promedio Movil" = Table.ExpandTableColumn(#"Removed other columns", "Data", Table.ColumnNames(#"Filtered rows <> estacionalidad") & {"RollingAverage"}),
  #"Changed column type" = Table.TransformColumnTypes(#"ExpandedTable Calculo Promedio Movil", {{"Fecha", type date}}),
  FechaMax = List.Max(#"Changed column type"[Fecha]),
  FechaMaxProyeccion = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type", "FechaMaxProyeccion", each FechaMax), {{"FechaMaxProyeccion", type date}})
in
  FechaMaxProyeccion

Consulta específica

1. ¿Cómo podría optimizar el cálculo del promedio móvil, especialmente en las secciones que utilizan List.Generate y List.LastN? Sospecho que estas funciones generan sobrecarga debido al tamaño de los datos.
2. ¿Existen mejores prácticas o alternativas para manejar este tipo de cálculos cuando se trabaja con grandes volúmenes de datos en Power Query?
3. ¿Qué estrategias generales recomiendan para mejorar el rendimiento en Dataflows de Power BI Service con este tipo de procesos?
   
   Quedo atento a sus respuestas, muchas gracias!!

[lbendlin · Accepted solution]

Try a Table.Buffer over a as well

 a = Table.Buffer(Table.SelectRows(BufferedTable, each [Mark_Real_Proyeccion] = "Real")),

Basically - try using a buffer whereever you are refrencing an entiry multiple times.

Monitor resource utilization - Table.Buffer is not free.

[lbendlin · Accepted solution]

remove some of the buffers again and add in other places. Sometimes buffers make it slower.

If you want real help then you would want to provide sample data that clearly shows the issue.  So more than a handful of rows.

[v-nmadadi-msft]

Hi @luis-fer-va ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @luis-fer-va ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thanks and Regards

[v-nmadadi-msft]

Hi @luis-fer-va ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3]

Hi @luis-fer-va, if you provide sample dummy data with expected result - I can give it a try.

[lbendlin]

At a minimum use Table.Buffer on a and d.   What's the cardinality of your group columns 

"Pk_EmpresaCuentaTercero","Escenario_ID"

?  How many groups?

[luis-fer-va]

Hola @lbendlin actualmente son 105 grupos x 31 = 3.255 iteracciones, use Table.Buffer pero se sigue demorando te comparto el code. sabes que otra solución podria tomar?

GroupedTable = Table.Group(
    #"Filtered rows <> estacionalidad",
    {"Pk_EmpresaCuentaTercero"},
    {
        {"Data", each
            let
                // Aplicar Table.Buffer para evitar lecturas repetidas
                BufferedTable = Table.Buffer(_),

                // Filtrar solo las filas "Real"
                a = Table.SelectRows(BufferedTable, each [Mark_Real_Proyeccion] = "Real"),
                
                // Obtener el valor máximo de "Index"
                b = List.Max(a[Index]),
                
                // Obtener el número de meses de la primera fila
                Numero_de_Meses = a{0}[Numero de Meses],
                
                // Obtener los últimos "Numero_de_Meses" valores de "Importe"
                c = List.LastN(a[Importe], Numero_de_Meses),
                
                // Contar el número de filas en el grupo
                d = Table.RowCount(BufferedTable),

                // Crear un buffer de la columna "Importe" para evitar cálculos repetidos
                ImporteList = List.Buffer(BufferedTable[Importe]),
                
                // Generar la lista de promedios rodantes
                RollingAverages = List.Generate(
                    () => [x = 0, y = c],
                    each [x] < d,
                    each [y = List.Combine({[y], {List.Average(List.LastN([y], Numero_de_Meses))}}), x = [x] + 1],
                    each List.Average(List.LastN([y], Numero_de_Meses))
                ),
                
                // Agregar la columna de promedio rodante
                UpdatedData = Table.AddColumn(
                    BufferedTable,
                    "RollingAverage",
                    each 
                        let
                            idx = [Index] - b - 1
                        in
                            if idx >= 0 then 
                                RollingAverages{idx}
                            else 
                                [Importe] // Si el índice es negativo, asignar null
                )
            in
                UpdatedData
        }
    }
)

Muchas gracias por tu ayuda @lbendlin 

[lbendlin · Accepted solution]

Try a Table.Buffer over a as well

 a = Table.Buffer(Table.SelectRows(BufferedTable, each [Mark_Real_Proyeccion] = "Real")),

Basically - try using a buffer whereever you are refrencing an entiry multiple times.

Monitor resource utilization - Table.Buffer is not free.

[luis-fer-va]

Muchas gracias @lbendlin redujo el tiempo en un 15% sin embargo sigue demorando, que otras alternativas recomiendas @lbendlin en este tipo de desarrollos?

[lbendlin · Accepted solution]

remove some of the buffers again and add in other places. Sometimes buffers make it slower.

If you want real help then you would want to provide sample data that clearly shows the issue.  So more than a handful of rows.

[luis-fer-va]

Saludos, @lbendlin 

Te comparto una muestra de los datos. Hemos identificado que Power Query (Dataflow) se queda corto para este procesamiento específico debido a la cantidad de iteraciones necesarias por fila. Este proceso se ejecuta cuando el usuario presiona un botón en Power Apps, lo cual desencadena un flujo en Power Automate.

Dado que este flujo es una "proyección", los usuarios suelen ejecutarlo múltiples veces al día, esperando una respuesta rápida para poder visualizar el informe inmediatamente después. Esto implica la necesidad de un procesamiento eficiente y optimizado.

¿Podrías, por favor, proporcionarnos una solución que permita llevar a cabo este procesamiento de manera más efectiva, basándote en tu experiencia? Adjunto una muestra de los datos en el siguiente enlace.

DataMuestra 

[v-nmadadi-msft · Accepted solution]

Hi @luis-fer-va ,
Thanks for reaching out to the Microsoft fabric community forum.

Please check out this document: Best practices when working with Power Query - Power Query | Microsoft Learn
Especially check out the part containing "Create reusable functions" If you find yourself in a situation where you need to apply the same set of transformations to different values, creating a Power Query custom function that can be reused as many times as you need could be beneficial.

Check this document: Why does my query run multiple times - Power Query | Microsoft Learn and try disabling features like background analysis.

Please try these steps and check if they result in improvement of efficiency. I hope my suggestions give some ideas on how to improve efficiency.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards