adding row

Hi wsindharta,
Thank you for reaching out to the Microsoft fabric community forum. Thank you SundarRaj, ZhangKun, BeaBF, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

M Query:

let

    // Sample source table

    Source = Table.FromRows({

        {"A123789", "06181819", "0120", "23/01/2022", #datetime(2025, 5, 2, 20, 25, 0), #datetime(2025, 5, 5, 8, 45, 35)}

    }, {"WorkCenter", "WorkOrder", "Operation", "ProductName", "StartTime", "EndTime"}),



    // Add duration in seconds

    AddDuration = Table.AddColumn(Source, "DurationSec", each Duration.TotalSeconds([EndTime] - [StartTime]), type number),



    // Generate list of dates for each row

    AddDateList = Table.AddColumn(AddDuration, "DateList", each List.Dates(

        Date.From([StartTime]),

        Duration.Days([EndTime] - [StartTime]) + 1,

        #duration(1, 0, 0, 0)

    )),



    // Expand date list into rows

    ExpandedDates = Table.ExpandListColumn(AddDateList, "DateList"),



    // Add start and end time for each day

    AddDayTimes = Table.AddColumn(ExpandedDates, "Start_End", each

        let

            currentDate = [DateList],

            startTime = if Date.From([StartTime]) = currentDate then [StartTime] else #datetime(Date.Year(currentDate), Date.Month(currentDate), Date.Day(currentDate), 0, 0, 0),

            endTime = if Date.From([EndTime]) = currentDate then [EndTime] else #datetime(Date.Year(currentDate), Date.Month(currentDate), Date.Day(currentDate), 23, 59, 59)

        in

            [StartTime = startTime, EndTime = endTime]

    ),



    // Expand start/end time record

    RemoveOriginalTimes = Table.RemoveColumns(AddDayTimes, {"StartTime", "EndTime"}),

    ExpandedTimes = Table.ExpandRecordColumn(RemoveOriginalTimes, "Start_End", {"StartTime", "EndTime"}),





    // Recalculate duration

    RecalcDuration = Table.AddColumn(ExpandedTimes, "Duration", each Duration.TotalSeconds([EndTime] - [StartTime]), type number),



    // Add date column

    Final = Table.RenameColumns(Table.SelectColumns(RecalcDuration, {"WorkCenter", "WorkOrder", "Operation", "ProductName", "StartTime", "EndTime", "Duration", "DateList"}), {"DateList", "Date"})

in

    Final

Output: Go to the table view see this output:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.