List.Max filter on date degrading Power query performance

[Vaishali04]

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

[watkinnc]

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

[Vaishali04]

Hi thanks for ur reply

In my case datazone is different and max data is always less than localnow() date . He we are using localnow() but  I  want to use max of date column of the data .

[V-pazhen-msft]

@Vaishali04 

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.

[Vaishali04]

Hi , Thanks for ur reply 

Indexing will not work in my case there are multiple rows for single date in my data

[watkinnc]

Try buffering a distinct list:

#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each [date] >=Date.AddDays( List.Max(List.Buffer(List.Distinct(_[date]))),-7))

--Nate