Forum Discussion

hanuraolm's avatar
hanuraolm
Helper I
4 years ago
Solved

populate same records into multiple based on hours spend.

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

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago
    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

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago
    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's avatar
      hanuraolm
      Helper I
      4 years ago

      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.