Forum Discussion
Filter date ranges for previous week
Hello,
I have the following Submissions table in Power Query. I would like to create a new table which contains only the rows where the ranges of dates from the start to end dates overlap in the previous week, based on today's date. Note: Every week is from Monday to Sunday.
My suggestion would be to expand the range of dates between the Start date and End date. The next step would be to restrict the rows to only the previous week's dates (from 04/04/2022 to 10/04/2022). I don't know how to do this in Power Query or if it is the best approach to solve this problem, so any advice is welcome!
Submissions table:
| Student name | Reason | Start date | End date |
| Cosamaloapan de Carpio | late | 28/03/2022 | 02/04/2022 |
| Zagrodno | sick | 01/04/2022 | 05/04/2022 |
| Luntas | sick | 05/04/2022 | 08/04/2022 |
| Frei Paulo | blank | 07/04/2022 | 14/04/2022 |
| Seedorf | blank | 28/03/2022 | 11/04/2022 |
| Bellegarde | sick | 18/04/2022 | 22/04/2022 |
| Gangarampur | late | 28/03/2022 | 20/04/2022 |
| Luntas | sick | 01/04/2022 | 10/04/2022 |
| Zagrodno | blank | 07/04/2022 | 09/04/2022 |
| Gangarampur | sick | 04/04/2022 | 04/04/2022 |
The results should be:
| Student name | Reason | Start date | End date |
| Zagrodno | sick | 01/04/2022 | 05/04/2022 |
| Luntas | sick | 01/04/2022 | 10/04/2022 |
| Gangarampur | sick | 04/04/2022 | 04/04/2022 |
| Luntas | sick | 05/04/2022 | 08/04/2022 |
| Frei Paulo | blank | 07/04/2022 | 14/04/2022 |
| Zagrodno | blank | 07/04/2022 | 09/04/2022 |
| Seedorf | blank | 28/03/2022 | 11/04/2022 |
| Gangarampur | late | 28/03/2022 | 20/04/2022 |
Yes, you are using UK date format whereas I used US date format.
Use this code which has dates in UK format.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBDoIwEER/hfRMQlsh4lUSvXgw8SbhsNqVEEpLCvy/RaNuE+G0meTNZHbKkhV2gA60hR5MpDAqwPWNZTHTMKI/Mk/4JpFcSi+4THj6FlVcsivUzioz00Nzb2dAfAEvsoA+TWaEgbAZZfOAPThsojNMes6+aTAvw5YYRBoYLojKugehg+JCBPQetcYanMJfHZGTdBk+egTjaej6yS0sI/n6r3QXwZdW/Psp3610+eSn1ECmqZ4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student name" = _t, Reason = _t, #"Start date" = _t, #"End date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start date", type date}, {"End date", type date}}), StartofPreviousWeekDate = Date.StartOfWeek(Date.AddDays(Date.From(DateTime.FixedLocalNow()),-7),1), EndofPreviousWeekDate = Date.EndOfWeek(Date.AddDays(Date.From(DateTime.FixedLocalNow()),-7),1), Result = Table.SelectRows(#"Changed Type", (x)=> x[End date]>=StartofPreviousWeekDate and x[Start date]<=EndofPreviousWeekDate) in Result
12 Replies
Frei Paulo should not be part of the output as range of dates from the start to end dates does not overlap in the previous week. Its end date is 14-Apr-22 which is outside the range.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdCxCoMwEAbgV5HMgkmq1K4V2qVDoVvF4dpcRYyJRH3/aqW9CNopB/k47v/znGW2gwa0hRZMoDDIwLWVZSHT0OP47CKZRpJLOc5xJOexCHN2h9JZZSbaVc/68y1IJiQvg+mh811CLiV3clgFVxj0tPOhwcx4T1jEpG+IyrqXR5enCkH2iFpjCU7h4lo/mJfsDGa00LSD2+yB/4vn1SD4amOr6Q5bJ/wWx4S/TRRv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student name" = _t, Reason = _t, #"Start date" = _t, #"End date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start date", type date}, {"End date", type date}}), StartofPreviousWeekDate = Date.StartOfWeek(Date.AddDays(Date.From(DateTime.FixedLocalNow()),-7),1), EndofPreviousWeekDate = Date.EndOfWeek(Date.AddDays(Date.From(DateTime.FixedLocalNow()),-7),1), Result = Table.SelectRows(#"Changed Type", (x)=> x[Start date]>=StartofPreviousWeekDate and x[End date]<=EndofPreviousWeekDate) in ResultHi Vijay_A_Verma Thanks for your reply! Sorry if i was not clear on the dates overlap issue but what i meant was that if any date within the range is in the previous week, then that row should be kept. In the example results, Frei Paulo should be in the results since the dates 07/04 until 10/04 are in the previous week.
Is it possible to implement this functionality?
Everything is possible but I need following clarity. As per your logic even following should be part of result as dates are overlapping within Monday to Sunday of previous week i.e. between 4-10 Apr.
Zagrodno sick 1/4/2022 5/4/2022 Seedorf blank 28/03/2022 11/4/2022 Gangarampur late 28/03/2022 20/04/2022