Improve Speed of SUMIF in Power Query

[Gherty0011]

I found this previous post that solved my problem for recreating a SUMIF in a column in Power Query. https://community.powerbi.com/t5/Power-Query/Create-sumif-in-PowerQuery-not-DAX-and-not-use-group-by...

I filtered my dataset off over 101K rows down to 52 rows to test and worked perfectly. My issue now is performance where even a simple result dividing the resulting column 8 (8 hours in a day) make the query run extremely slow. It takes 3-4 minutes to load just those test 52 rows. I then changed that new column to % type and that tool takes 3-4 minutes to load.

The initial solution takes 3-4 minutes, then any proceeding steps take the same time as it shows me it loads each row at a time. This worries me because I need to iterate over 101K rows and proceeding transformation takes a long time. 

is there a way to fix this to perform better? I didn't have any problem working with the dataset before this formula. 

[davehus · Accepted solution]

Hi @Gherty0011 ,

There is an option in Power Query to improve query performance called Table.Buffer. It may or may not help. Have a look at the documentation first anyway to see if it will help you. I've used it before and it has worked well for me but it was on a smaller dataset.

https://docs.microsoft.com/en-us/powerquery-m/table-buffer

D

Did I help you today? Please accept my solution and hit the Kudos button.