I am really having trouble with the slow speed of Power Query after I make some necessary transformations. First, I am using
Microsoft 365 MSO (16.0.14326.20384) 64-bit
Edition Windows 10 Pro
Installed on 7/27/2021
OS build 19043.1237
Computer has 64GB of RAM.
I am working with a large .csv dataset that has 36 million rows. What I have noticed is the following - if I leave it totally unfiltered or unsorted, I can make transformations quickly, often very quickly. For example, if I add an index column, it takes about 1 second. But as soon as I filter the data or sort it, the same index column takes many minutes, so many that I have never let it finish. A more complex custom column using M Code took over and hour before I stopped it These transformations are necessary, particularly the sorting, so I can't leave them out. Btw, I have similar problems with smaller datasets, though not as dramatic, as every step after sorting and filtering is much slower.
Is there a reason why the filtering and sorting slows everything down so much? And can anything be done to improve the speed?