Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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 .
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.
Hi , Thanks for ur reply
Indexing will not work in my case there are multiple rows for single date in my data
Try buffering a distinct list:
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each [date] >=Date.AddDays( List.Max(List.Buffer(List.Distinct(_[date]))),-7))
--Nate
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.