cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrkeastwd
Frequent Visitor

Add rows based on time between existing rows

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...

mrkeastwd_0-1620831153890.png

 

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...

mrkeastwd_1-1620831328148.png

 

2 ACCEPTED SOLUTIONS
watkinnc
Solution Sage
Solution Sage

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

View solution in original post

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!

 

mrkeastwd_0-1620918990370.png

 

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!  

View solution in original post

7 REPLIES 7
watkinnc
Solution Sage
Solution Sage

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

View solution in original post

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!

 

mrkeastwd_0-1620918990370.png

 

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!  

View solution in original post

mrkeastwd
Frequent Visitor

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
Solution Sage
Solution Sage

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

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...

mrkeastwd_0-1620842005127.png

 

watkinnc
Solution Sage
Solution Sage

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

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors