How to split time interval rows into quarters

[Mengeltje]

Tried this with Copilot but didn't get it worked.

It's about the registration of time of people working. I get an Excel with for example the following info:

Column 1	Column 2	Column 3	Column 4
19-1-2024 00:00	19-1-2024 12:15	19-1-2024 13:00	Lunch
20-1-2024 00:00	20-1-2024 12:15	20-1-2024 12:45	Out

This file is imported in Power BI but I need this per quarter:

Column 1	Column 2	Column 3	Column 4
19-1-2024 00:00	19-1-2024 12:15	19-1-2024 12:30	Lunch
19-1-2024 00:00	19-1-2024 12:30	19-1-2024 12:45	Lunch
19-1-2024 00:00	19-1-2024 12:45	19-1-2024 13:00	Lunch
20-1-2024 00:00	20-1-2024 12:15	20-1-2024 12:30	Out
20-1-2024 00:00	20-1-2024 12:30	20-1-2024 12:45	Out

Hope someone can help me!

Thanks in advance!

[DataNinja777 · Accepted solution]

Hi @Mengeltje ,

To transform your data into 15-minute intervals in Power BI, you can use Power Query to automate the process. Load your table into Power Query and open the Advanced Editor. Replace the existing code with the following script:

let
    Source = <Your Table Name>,  // Replace with your actual table name
    AddQuarterIntervals = Table.AddColumn(Source, "QuarterIntervals", each 
        List.Transform(
            List.Numbers(0, Number.RoundUp(Duration.TotalMinutes(Duration.From([Column3] - [Column2])) / 15)),
            each [Column2] + #duration(0, 0, 15) * _
        )
    ),
    ExpandQuarterIntervals = Table.ExpandListColumn(AddQuarterIntervals, "QuarterIntervals"),
    AdjustEndTime = Table.AddColumn(ExpandQuarterIntervals, "NewEndTime", each 
        if [QuarterIntervals] + #duration(0, 0, 15) > [Column3] then [Column3] else [QuarterIntervals] + #duration(0, 0, 15)
    ),
    RemoveExtraColumns = Table.SelectColumns(AdjustEndTime, {"Column1", "QuarterIntervals", "NewEndTime", "Column4"}),
    RenameColumns = Table.RenameColumns(RemoveExtraColumns, {{"QuarterIntervals", "Column2"}, {"NewEndTime", "Column3"}})
in
    RenameColumns

This script works by first generating a list of 15-minute intervals between the start (Column2) and end (Column3) times for each row. These intervals are expanded into individual rows to create the desired breakdown. A new column (NewEndTime) is calculated to ensure that each interval's end time aligns with either the next 15-minute mark or the original end time. Unnecessary columns used for intermediate calculations are then removed, leaving only the required fields. Finally, the columns are renamed to match your desired format.

When applied, this script will transform your data so that each original time range is split into rows representing 15-minute intervals, while preserving all necessary information from the original table. If you encounter any issues or need further clarification, feel free to ask.

Best regards,

[danextian · Accepted solution]

Hi @Mengeltje 

Here's a sample query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzDUNzIwMlEwsDIwUNJBFjE0sjI0RRMyhqjyKc1LzlCK1YlWMjJANwFJBGYCqpAJSMi/tEQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Start = _t, End = _t, Remarks = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Start", type datetime}, {"End", type datetime}, {"Remarks", type text}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Date", DateTime.Date, type date}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Date", "Split", each let
    // Define the start and end times
    StartTime =[Start],
    EndTime = [End],

    // Generate a list of times with 15-minute intervals
    TimeList = List.Generate(
        () => StartTime, 
        each _ < EndTime, 
        each _ + #duration(0, 0, 15, 0)  // Add 15 minutes using #duration
    ),

    // Convert the list into a table
    TimeTable = Table.FromList(TimeList, Splitter.SplitByNothing(), {"Start Time"}),

    // Add the End Time by adding 15 minutes to each Start Time
    AddEndTime = Table.AddColumn(TimeTable, "End Time", each [Start Time] + #duration(0, 0, 15, 0))
in
    AddEndTime),
    //Expanded the generate column of time tables
    #"Expanded Split" = Table.ExpandTableColumn(#"Added Custom", "Split", {"Start Time", "End Time"}, {"Start Time", "End Time"})
in
    #"Expanded Split"

[DataNinja777 · Accepted solution]

Hi @Mengeltje ,

To transform your data into 15-minute intervals in Power BI, you can use Power Query to automate the process. Load your table into Power Query and open the Advanced Editor. Replace the existing code with the following script:

let
    Source = <Your Table Name>,  // Replace with your actual table name
    AddQuarterIntervals = Table.AddColumn(Source, "QuarterIntervals", each 
        List.Transform(
            List.Numbers(0, Number.RoundUp(Duration.TotalMinutes(Duration.From([Column3] - [Column2])) / 15)),
            each [Column2] + #duration(0, 0, 15) * _
        )
    ),
    ExpandQuarterIntervals = Table.ExpandListColumn(AddQuarterIntervals, "QuarterIntervals"),
    AdjustEndTime = Table.AddColumn(ExpandQuarterIntervals, "NewEndTime", each 
        if [QuarterIntervals] + #duration(0, 0, 15) > [Column3] then [Column3] else [QuarterIntervals] + #duration(0, 0, 15)
    ),
    RemoveExtraColumns = Table.SelectColumns(AdjustEndTime, {"Column1", "QuarterIntervals", "NewEndTime", "Column4"}),
    RenameColumns = Table.RenameColumns(RemoveExtraColumns, {{"QuarterIntervals", "Column2"}, {"NewEndTime", "Column3"}})
in
    RenameColumns

This script works by first generating a list of 15-minute intervals between the start (Column2) and end (Column3) times for each row. These intervals are expanded into individual rows to create the desired breakdown. A new column (NewEndTime) is calculated to ensure that each interval's end time aligns with either the next 15-minute mark or the original end time. Unnecessary columns used for intermediate calculations are then removed, leaving only the required fields. Finally, the columns are renamed to match your desired format.

When applied, this script will transform your data so that each original time range is split into rows representing 15-minute intervals, while preserving all necessary information from the original table. If you encounter any issues or need further clarification, feel free to ask.

Best regards,