populate same records into multiple based on hours spend.

[hanuraolm]

if the particular record downtime (completion time - accept time)e is more than 1 hour, then need to populate multiple records for each hour, else the same record.  see the below

source

result

[wdx223_Daniel · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    fx=(dt,et)=>List.Min({Time.EndOfHour(dt)-#duration(0,0,0,1),et}),
    DT=(ct,at)=>Duration.TotalHours(ct-at),
    Custom1 = Table.FromRecords(
                                List.TransformMany(
                                                   Table.ToRecords(Source),
                                                   each List.Generate(
                                                                      ()=>_&[
                                                                             CompleteTime=fx([AcceptTime],[CompleteTime]),
                                                                             DownTime=DT(CompleteTime,[AcceptTime])
                                                                            ],
                                                                      each [AcceptTime]<=[CompleteTime],
                                                                      (x)=>x&[
                                                                              AcceptTime=Time.StartOfHour(x[CompleteTime]+#duration(0,1,0,0)),
                                                                              CompleteTime=fx(AcceptTime,[CompleteTime]),
                                                                              DownTime=DT(CompleteTime,AcceptTime)]
                                                                     ),
                                                   (x,y)=>y
                                                  )
                               )
in
    Custom1

[Anonymous · Accepted solution]

Hi @hanuraolm ,

Please go to "Advanced Editor" dialog, and paste the @wdx223_Daniel 's M syntax in it.

In your case, you may need to make some changes as shown below:

Best Regards,
Eyelyn Qin

[wdx223_Daniel · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    fx=(dt,et)=>List.Min({Time.EndOfHour(dt)-#duration(0,0,0,1),et}),
    DT=(ct,at)=>Duration.TotalHours(ct-at),
    Custom1 = Table.FromRecords(
                                List.TransformMany(
                                                   Table.ToRecords(Source),
                                                   each List.Generate(
                                                                      ()=>_&[
                                                                             CompleteTime=fx([AcceptTime],[CompleteTime]),
                                                                             DownTime=DT(CompleteTime,[AcceptTime])
                                                                            ],
                                                                      each [AcceptTime]<=[CompleteTime],
                                                                      (x)=>x&[
                                                                              AcceptTime=Time.StartOfHour(x[CompleteTime]+#duration(0,1,0,0)),
                                                                              CompleteTime=fx(AcceptTime,[CompleteTime]),
                                                                              DownTime=DT(CompleteTime,AcceptTime)]
                                                                     ),
                                                   (x,y)=>y
                                                  )
                               )
in
    Custom1

[hanuraolm]

thanks for the details. Could you share more details about what you have imported and the final result of that. If you can share the PowerBI file that would help us to set up as per the need.