Forum Discussion
Solved
Grouping date and time
Hi, I would like to create a calculated column to create batches based on date and time. For example: 01/01/2022 11:00:00 AM to 01/02/2022 11:00:00AM Batch1 01/02/2022 11:00:00 AM to 01/03/2...
if you just want to select the dates in your table (May skip Weekends, holidays ect)
Add Conditional Column to table in Power Query:
= Table.AddColumn(#"Renamed Columns", "Adj Date", each if [Time] >= #time(11, 0, 0) then [Date] else (Date.AddDays([Date],-1)))Create a Copy of your Table in Power Query name it Batch
Remove all Columns except for Adj Date
Remove duplicates
add index starting at 1 this will be your batch numberJoin to your financials table: [Adj Date] --> Batch Table: [Adj Date]
change index field to not summarize
This is how the data looks like:
| 1/1/2022 9:00 |
| 1/1/2022 10:00 |
| 1/1/2022 11:00 |
| 1/1/2022 12:00 |
| 1/1/2022 13:00 |
| 1/1/2022 14:00 |
| 1/1/2022 15:00 |
| 1/1/2022 16:00 |
| 2/1/2022 9:00 |
| 2/1/2022 10:00 |
| 2/1/2022 11:00 |
| 2/1/2022 12:00 |
| 2/1/2022 13:00 |
| 2/1/2022 14:00 |
| 2/1/2022 15:00 |
| 2/1/2022 16:00 |
| 3/1/2022 9:00 |
| 3/1/2022 10:00 |
| 3/1/2022 11:00 |
| 3/1/2022 12:00 |
| 3/1/2022 13:00 |
| 3/1/2022 14:00 |
| 3/1/2022 15:00 |
| 3/1/2022 16:00 |
| 5/1/2022 9:00 |
| 5/1/2022 10:00 |
| 5/1/2022 11:00 |
| 5/1/2022 12:00 |
| 5/1/2022 13:00 |
| 5/1/2022 14:00 |
| 5/1/2022 15:00 |
| 5/1/2022 16:00 |
The calculated column should result in the following:
| 1/1/2022 9:00 | Batch 0 |
| 1/1/2022 10:00 | Batch 0 |
| 1/1/2022 11:00 | Batch 1 |
| 1/1/2022 12:00 | Batch 1 |
| 1/1/2022 13:00 | Batch 1 |
| 1/1/2022 14:00 | Batch 1 |
| 1/1/2022 15:00 | Batch 1 |
| 1/1/2022 16:00 | Batch 1 |
| 2/1/2022 9:00 | Batch 1 |
| 2/1/2022 10:00 | Batch 1 |
| 2/1/2022 11:00 | Batch 2 |
| 2/1/2022 12:00 | Batch 2 |
| 2/1/2022 13:00 | Batch 2 |
| 2/1/2022 14:00 | Batch 2 |
| 2/1/2022 15:00 | Batch 2 |
| 2/1/2022 16:00 | Batch 2 |
| 3/1/2022 9:00 | Batch 2 |
| 3/1/2022 10:00 | Batch 2 |
| 3/1/2022 11:00 | Batch 3 |
| 3/1/2022 12:00 | Batch 3 |
| 3/1/2022 13:00 | Batch 3 |
| 3/1/2022 14:00 | Batch 3 |
| 3/1/2022 15:00 | Batch 3 |
| 3/1/2022 16:00 | Batch 3 |
| 5/1/2022 9:00 | Batch 3 |
| 5/1/2022 10:00 | Batch 3 |
| 5/1/2022 11:00 | Batch 4 |
| 5/1/2022 12:00 | Batch 4 |
| 5/1/2022 13:00 | Batch 4 |
| 5/1/2022 14:00 | Batch 4 |
| 5/1/2022 15:00 | Batch 4 |
| 5/1/2022 16:00 | Batch 4 |
I am wondering if there is any way of doing this through switch or through loop functions?