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

kitgo2's avatar
kitgo2
Advocate I
4 years ago

You might be able use the Day of the month or day of the year for your batch number: 

*Batch = If(financials[Time] >= Time(11,0,0), Day(financials[Date]), Day(financials[Date] -1))
 
  • kitgo2's avatar
    kitgo2
    Advocate I
    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