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

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi adityamalik1234 

you create a table 'Batches' which contains three columns [From], [To] and [Batch]
then the new calculated column in Table1 would be

MAXX (

FILTER ( Batches, Batches [From] <= Table1[Date] && Batches [To] > Table1[Date] ), Batches[Batch] )

adityamalik1234's avatar
adityamalik1234
Frequent Visitor
4 years ago

Hi Tamerj,

 

thanks for your reply. I am looking for help on creating the logic for the Batch number. So the earliest date to next date is batch1 and so on.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    adityamalik1234 

    Ok. Please share some sample data to understand how does it look like.