Forum Discussion
Filter by highest and second highest value in Power Query
Hi everyone, I have a table containing data from different months. I want to filter for the latest and the previous months only. My question is, what is the most efficient way of doing this? ...
What I really don't understand is that it is the filtering that always slows the loading down so much, it's not the merge & expand, nor the calculation of List.MaxN.
When I simply filter manually like 'yyyymm' = "202207" it is quick, ~30 seconds.
Whenever I create a calculated column comparing the highest value of 'yyyymm' to 'yyyymm' using List.Max it's still only ~30 seconds to create the column containing the 1s and 0s (1 if 'yyyymm' = List.Max('yyyymm')), but when I do the filter on that column, that is the step which is killing my dataflow. It's the same with Merge+Expand+filter.
Why is the filter after a List.Max or Merge+Expand so heavy on the resource?
(my main question is still how to solve it, but I'm really curious about this one)