adding row

wsindharta

Hi,

i need help to add row into my data.

i have power bi that connect directly with SQL database. below is the sample of data

this is the downtime data feed. focus on highlighted yellow line, this particular down time happen accross the day from 05/02/2025 20:25 until 05/05/2025 08:45:35.

is it possible to breakdown this data into the below format ? basically to generate another 3 row so the one line is not cross over the date.

i believe this is very common in manufacturing area, the objective so we can drill down the downtime by date. with one line cross the date, it will be difficult to plot / visualize. as someday will be missing.

thanks

v-kpoloju-msft

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.

v-kpoloju-msft

Hi @wsindharta,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

SundarRaj

Hi @wsindharta , here's another solution you could look at. I'll attach the images and the code. I'll attach the file for your reference. Let me if I cleared your query. Thanks!
https://docs.google.com/spreadsheets/d/1V_HewD8xOXVC_mFRx5DLRG6Rp4_Zca9B/edit?usp=sharing&ouid=10475...

wsindharta

Hi @SundarRaj thank you for reply, but i don't see the attached file, the link is goes to my original file. please advise

SundarRaj

Hi @wsindharta , this should give you the downloadable excel file. Launch the Power Query Editor and you shall see the table there. Thanks. Let me know if it is resolved.
https://docs.google.com/spreadsheets/d/1mRs_F6QpJj4w2obI0i_RB-aEx90XwN6s/export?format=xlsx&ouid=104...

ZhangKun

You can download the attachment and see if it solves your problem.

wsindharta

hi @ZhangKun

this is great and work fine ! however when i try to paste the code into my original query. i got error

"Expression.Error: Evaluation resulted in a stack overflow and cannot continue."

please help

below is the original query :

let
Source = Sql.Database("MLXSNGXVWPOPDB99", "STDS", [Query=****),
#"Removed Columns" = Table.RemoveColumns(Source,{"Department", "Operation", "Tool", "Location", "Area"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Downtime Duration", each if [batchid]=null then [Duration] else [Duration]/2),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Downtime Duration", type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Duration"}),
#"Changed Type11" = Table.TransformColumnTypes(#"Removed Columns1",{{"StartTime", type number}, {"EndTime", type number}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type11", "Custom", each if [EndTime]= null then [StartTime]+[Downtime Duration]/(60*24) else [EndTime]),
#"Changed Type12" = Table.TransformColumnTypes(#"Added Custom5",{{"Custom", type datetime}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
#"Removed Columns7" = Table.RemoveColumns(#"Changed Type12",{ "EndTime"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns7",{{"Custom", "EndTime"}}),
fxDatesToList = (r) =>
let
// simplify variable references
s = r[StartTime],
e = r[EndTime],
sd = Date.From(s),
ed = Date.From(e)
in
List.Transform(
List.Dates(sd, Duration.Days(ed - sd) + 1, #duration(1, 0, 0, 0)),
each r & [
StartTime = if _ = sd then s else _ & #time(0, 0, 0),
EndTime = if _ = ed then e else _ & #time(23, 59, 59),
// due to the date system problem, the full day should be specified as 1440 minutes.
Duration = if _ = sd or _ = ed then Duration.TotalMinutes(EndTime - StartTime) else 1440
]
),
result = Table.FromRecords(
List.Accumulate(
Table.ToRecords(#"Renamed Columns4"),
{},
(s, v) =>
if Date.Day(v[StartTime]) <> Date.Day(v[EndTime]) then s & fxDatesToList(v) else s & {v}
),
// Convert records to table and restore column types
Value.Type(#"Renamed Columns4")
)
in
result

wsindharta

hi @BeaBF

this is the link to the sample data in google drive. there are few lines that need to break down in this sample data (those highlighted in yellow). thanks

https://docs.google.com/spreadsheets/d/1kc3taz9MwkTqzanQshnJlauj1NNfS-b1/edit?usp=drive_link&ouid=10...