Conditional Filtering

[Anonymous]

Hello everyone,

I got this [Date] column and I would like to filter it but conditionally, if today is Monday so I need it to filter [Date] to keep only Sunday’s, Saturday’s and Friday’s entries that preceded today only, otherwise, to filter [Date] to keep only yesterday’s entries, how to do that?

Another question related to data models in excel, is there a way to load them in tables, not power pivot?

[edhans · Accepted solution]

Add a new blank query and go to the advanced editor. Delete all the code and paste this in:

let
    Source = if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 0 then Date.AddDays(DateTime.Date(DateTime.LocalNow()), -3) else Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1)
in
    Source

If today is Monday, it will return a day 3 days in the past, otherwise 1 day in the past.

Call this query varDate. Set it not to load.

Now, go to your data and filter for any day to get the filter to work. Remove whatever date you selected (it might say #date(2022,3,25) - remove that code and use varDate.

It might look like this:

Table.SelectRows(#"Added Custom", each ([Dates] = varDate))

On the 2nd question, no. You can create a flat pivot table, but not a pure table. Those must come direclty from Power Query, not Power Query to Power Pivot then down to and Excel sheet.