Forum Discussion

adityamalik1234's avatar
adityamalik1234
Frequent Visitor
4 years ago
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...
Need Help
  • kitgo2's avatar
    kitgo2
    4 years ago

    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 number

     

    Join to your financials table: [Adj Date] --> Batch Table: [Adj Date]

    change index field to not summarize

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

Hi, adityamalik1234 ;

Such as kitgo2 said, you could create a column by dax.

Column = "Batch"&IF(TIME(HOUR( [datetime]),MINUTE( [datetime]),SECOND( [datetime]))<TIME(11,0,0),DAY([datetime])-1,DAY([datetime]))

The final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.