Add rows based on time between existing rows

[mrkeastwd]

The screen captures are from an Excel workbook in order to simplify and anonymize the data.  This is a project I'm working on in Power BI and trying to figure out how to do this with Power Query but am having no luck so far.  Any assistance would be appreciated.

I have a table that has data that looks like this...

and I am trying to figure out how to add rows in between existing rows where the time from EndTime to the next StartTime is greater than 5 minutes to capture all the undocumented time in a day for each user. 

The output should look something like this...

[watkinnc · Accepted solution]

OK I get it now.  I would add a new query that selects the rows that mee the criteria, replace values in that table, and then Append the table back to the original table.  First Duplicate your table, then:

Selections = Table.SelectRows(DuplicateTableName, each [UndocTime] >5),

Undoc = Table.ReplaceValues(Selections, each [WorkType], "UnDoc", Replacer.ReplaceText, {WorkType}),

Start = Table.AddColumn(Undoc, "Start Time", each [PreviousEndTime]),

End = Table.AddColumn(Start, "End Time", each [StartTime])

in End

Now you can append to a new Query:

Combined = Table.Combine(OriginalTable, DuplicateTableName[[User], [WorkType], [Start Time], [End Time]])

That should work, but I'm not sure how you can sort it correctly...

--Nate

[watkinnc · Accepted solution]

OK I get it now.  I would add a new query that selects the rows that mee the criteria, replace values in that table, and then Append the table back to the original table.  First Duplicate your table, then:

Selections = Table.SelectRows(DuplicateTableName, each [UndocTime] >5),

Undoc = Table.ReplaceValues(Selections, each [WorkType], "UnDoc", Replacer.ReplaceText, {WorkType}),

Start = Table.AddColumn(Undoc, "Start Time", each [PreviousEndTime]),

End = Table.AddColumn(Start, "End Time", each [StartTime])

in End

Now you can append to a new Query:

Combined = Table.Combine(OriginalTable, DuplicateTableName[[User], [WorkType], [Start Time], [End Time]])

That should work, but I'm not sure how you can sort it correctly...

--Nate

[mrkeastwd · Accepted solution]

Thanks Nate!

I was able to take what you gave me, with a few other manipulations and the end result is exactly what I've been trying to accomplish!

I used this Table.Sort(Source,{{"User", Order.Ascending}, {"StartTime", Order.Ascending}}) to sort the data.  Here is the final code for anyone that reads this and needs it...

First Table (WorkLog)...

let
    Source = Excel.Workbook(File.Contents("C:\MyFiles\SampleData-UndocTime.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User", type text}, {"WorkType", type text}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index2", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index1",{"Index", "Index2", "User", "WorkType", "StartTime", "EndTime"}),
    #"Add PreviousEndTime" = Table.AddColumn(#"Reordered Columns", "PreviousEndTime", each if [Index] = 0 then [StartTime] else #"Reordered Columns"{[Index]-1}[EndTime]),
    #"Add UndocTime" = Table.AddColumn(#"Add PreviousEndTime", "UndocTimeMin", each Duration.Minutes([StartTime]-[PreviousEndTime]))
in
    #"Add UndocTime"

This is the duplicate table to filter the records for undocumented time entries (WorkLogDupe)

let
    Source = WorkLog,
    Selections = Table.SelectRows(Source, each [UndocTimeMin] >5),
    Undoc = Table.ReplaceValue(Selections, each [WorkType], "UnDoc", Replacer.ReplaceValue,{"WorkType"}),
    UpdateUndocTimeMin = Table.ReplaceValue(Undoc, each [UndocTimeMin], 0, Replacer.ReplaceValue,{"UndocTimeMin"}),
    Start = Table.AddColumn(UpdateUndocTimeMin, "Start Time", each [PreviousEndTime]),
    End = Table.AddColumn(Start, "End Time", each [StartTime]),
    RemoveOldTimeColumns = Table.RemoveColumns(End,{"StartTime","EndTime"}),
    RenameNewTimeColumns = Table.RenameColumns(RemoveOldTimeColumns,{{"Start Time", "StartTime"},{"End Time", "EndTime"}}),
    #"Reordered Columns" = Table.ReorderColumns(RenameNewTimeColumns,{"Index", "Index2", "User", "WorkType", "StartTime", "EndTime", "PreviousEndTime", "UndocTimeMin"})
in
    #"Reordered Columns"

And this is the append as new final table (FullWorkLog)...

let
    Source = Table.Combine({WorkLog, WorkLogDupe}),
    #"Sorted Rows" = Table.Sort(Source,{{"User", Order.Ascending}, {"StartTime", Order.Ascending}})
in
    #"Sorted Rows"

Nate...Thank you very much for your help to accomplish this!  

[mrkeastwd]

Not sure if this will help with this issue...but here is the Power Query code I have.  I've added a single row successfully but can't figure out how to itterate through the existing rows and insert as needed the new ones.

let
    Source = Excel.Workbook(File.Contents("C:\MyFolder\SampleData-UndocTime.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User", type text}, {"WorkType", type text}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index2", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index1",{"Index", "Index2", "User", "WorkType", "StartTime", "EndTime"}),
    #"Add PreviousEndTime" = Table.AddColumn(#"Reordered Columns", "PreviousEndTime", each if [Index] = 0 then [StartTime] else #"Reordered Columns"{[Index]-1}[EndTime]),
    #"Add UndocTime" = Table.AddColumn(#"Add PreviousEndTime", "UndocTimeMin", each Duration.Minutes([StartTime]-[PreviousEndTime])),
    AddUndocRows= Table.InsertRows(#"Add PreviousEndTime",1,{
        [
            Index=1,
            Index2=2,
            User="John",
            WorkType="Undoc",
            StartTime=DateTime.FromText("1899-12-31 08:22:00 AM"),
            EndTime=DateTime.FromText("1899-12-31 08:39:00 AM"),
            PreviousStartTime=DateTime.FromText("1899-12-31 08:22:00 AM"),
            PreviousEndTime=DateTime.FromText("1899-12-31 08:39:00 AM"),
            UndocTimeMin=0
        ]})
in
    AddUndocRows

[watkinnc]

I might not have been as clear as I could have been. In my solution, you are adding the two index columns, and you will use those columns to merge the table with itself. In the Merge GUI, this table will be on top, on the bottom, choose the same table (labels "This table".  By selecting the 0 index on the first table, and the 1 index on the second table, you will end up with columns StartTime1 and EndTime1, basically moving each StartTime up one row, and then you can subtract each [StartTime1]-[EndTime]. 
--Nate

[mrkeastwd]

Thanks for clarifying Nate.  

I did misunderstand that.  I was able to do what you suggested, and added a column that calculates the time Undocumented time between the 2 lines and accounts for the negative value between the users, as well as the null value in the first row.  I have previously managed to get to this point although in a different way at a previous time over the days and days that I have been trying to figure this out.

I'm still left at the same point though, and that is how to add rows where there is undocumented time.  This is the part I'm really stuck on.  

if the UndocTimeMin >5, insert a new row that has the [User], [WorkType]=Undoc, [StartTime]=[PreviousEndTime], [EndTime]=[StartTime] 

Any help would be appreciated.  Here is screenshot of my existing dataset thus far...

[watkinnc]

You can add two index columns, one starting at 1, the other at zero, then LeftJoin to this very table in the GUI function, using the index starting at 1 on the left table, and starting at 0 on the right, as your matching keys. This will line up your values, then just subtract!

--Nate

[mrkeastwd]

Thanks Nate.

All the data I have is like the first screenshot.  I only have 1 table that contains the data, and I need to itterate through it and get the time differences and insert new rows (or create a new table that has the existing data with rows inserted).  The second screenshot above is what my desired output is, not a table that I already have.