Find last camera status

Yes, you can do this in the M lanaguge, note it won't be very efficient for large files.

For step 1...

Let's say your main query is called MainQuery.

1. Click the Event Time column and under Add column choose Date Only

2. Right click your MainQuery, and choose reference.

3. Click the Event Time column, choose Transform -> Date -> Earliest. This should give a single non-table value of the earliest datetime

4. Rename query to StartDate

5. Add a blank query with the following line, this will generate all days for your events:

= #table(type table [Date = date], List.Zip({List.Generate(() => StartDate, each _ <= DateTime.Date(DateTime.LocalNow()), each _ + #duration(1, 0, 0,0))}))

6. Add a custom column with the name "Last Event" with

= List.Distinct(MainQuery[Entity source]) 

7. Expand list column

8. Add a custom column wtih:

= Table.AddColumn(#"Expanded Source entity", "MostRecentEvent", each Table.Max(Table.SelectRows(MainTable, (row) => row[Date] <= [Date]), "Event timestamp"))

8. Rename query to something more appropiate.

Result: You now have a table with every source for every day, and this includes any gaps where a device didn't report anything in a day. Additionally there is now a column with "Last Event" which is the last event that occured by the end of that day.

Next steps: Add a custom column which extract various stats from the table Main query using the event source and date column. The Last Even column is used to determine if the camera was on at the start of the day or off.