Forum Discussion
Optimizar codigo power query promedio movil
- 1 year ago
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.
- 1 year ago
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.
- Anonymous1 year ago
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
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-va1 year agoFrequent Visitor
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
- lbendlin1 year ago
Super User
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-va1 year agoFrequent Visitor
Muchas gracias lbendlin redujo el tiempo en un 15% sin embargo sigue demorando, que otras alternativas recomiendas lbendlin en este tipo de desarrollos?