Forum Discussion
List.Max filter on date degrading Power query performance
Hi All,
I am using below step in query to limit data in my query. In below line am puting filter in query to just to load only last 7 days data. But this is taking too much time to execute in PBI desktop. Is there any alternate logic to optimize the performance.
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each [date] >=Date.AddDays( List.Max(#"Removed Columns"[date]),-7))
Please help if anyone has suggestions.
Regards
Vaishali
5 Replies
Anonymous
You can just sort the date column in descending order, then add an index to filter to <=7. This should be faster. BTW, if you have duplicated date, you would need to group by before the index column.
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] <= 7)
in
#"Filtered Rows"
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Actually, a much faster method is to calculate the variable before adding it to the function. So if your prior step was PriorStep:
Last7 = Date.AddDays(Date.From(DateTime.LocalNow()), -7),
NewTable = Table.SelectRows(PriorStep, each [date] >= Last7)
in NewTable
I don't know why we are wasting so much effort calculating T-7 from the whole big dataset, when we can just get the value once, easily, and just filter using the value. Sooooo much faster.
Love Power Query!
--Nate