CODEWITHSUNDEEP
powerquery
LeppyR64
LeppyR64

Reputation: 5339

Power Query - Find Time since First Record in Table

I have a query that pulls the username and entry time. I want to have the query contain the username, entry time, and time since the very first entry time in minutes.

Bob, 12/01/2019 18:00, 0 Carl, 12/01/2019 18:02, 2 Dave, 12/01/2019 18:30, 30

I have been able to create a query that calculates the minimum time from the table but I haven't been able join them, and it might be easier to do it in a single step anyways.

Upvotes: 0

Views: 44

Answers (1)

horseyride
horseyride

Reputation: 21298

Group by .. advanced .... on username. Add two new column names to pull both the maximum and the minimum of column entrytime. Then add column .. custom column ... and create a formula that subtracts the new max column from the minimum column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"username", type text}, {"entrytime", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"username"}, {{"In", each List.Min([entrytime]), type time}, {"Out", each List.Max([entrytime]), type time}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Out]-[In])
in #"Added Custom"

Above assumes you are looking at a data for single date. For data with multiple dates where the person never stays overnight, Group by .. advanced .... on both username & date.

Edit:

To compare each row to minimum time of everyone, get the minimum time of everyone

 MinTime = List.Min(Source[Time]),

Then create a new custom column that subtracts 

#"Added Custom" = Table.AddColumn(#"Prior Step", "Custom", each [Time]-MinTime)

Upvotes: 1

Related Questions