Find last camera status

[Anonymous]

Hello guys, im facing a big problem to solv and i hope you can help me so here it comes!

I will explain first what I need to do and then tell you how I think about starting to solve. I have a status report for cameras that I control at my company. Well, first I need to know for each camera that I have in my report what the current status is (online or offline). I don't have this clear in the report, what I have is a series of events that occur that say the camera is operating or not, for example if the event nr 74 appears I know the camera has stopped and if a 73 appears I know it returned to operate. another thing I need is how long the camera was offline on that particular day, so I need a formula in m language that calculates when the last event was and the one before it and that returns the difference between the events and tells me the time total of them.

Below is a print of my table.

[artemus]

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.

[Anonymous]

Great! i made it to the step 8 and I'm stuck again..here is the query, I'm forgetting smth.

let
Events = let
Lista = #table(type table [Date = date], List.Zip({List.Generate(() => DateTime.Date(StartDate), each _ <= DateTime.Date(DateTime.LocalNow()), each _ + #duration(1, 0, 0,0))})),
#"Last Event" = Table.AddColumn(#"Lista", "Last Event", each List.Distinct([Source entity]))
in
Lista,
#"Last Event" = Table.AddColumn(Events, "Last Event", each List.Distinct(fStatus[Source entity])),
#"Valores Extraídos" = Table.TransformColumns(#"Last Event", {"Last Event", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Personalização Adicionada" = Table.AddColumn(#"Valores Extraídos", "Personalizar", each Table.AddColumn(#"Last Event", "MostRecentEvent", each Table.Max(Table.SelectRows(fStatus, (row) => row[Data] <= fStatus[Event timestamp]), "Event timestamp"))),
#"Personalizar Expandido" = Table.ExpandTableColumn(#"Personalização Adicionada", "Personalizar", {"Date", "Last Event", "MostRecentEvent"}, {"Date.1", "Last Event.1", "MostRecentEvent"}),
#"Valores Extraídos1" = Table.TransformColumns(#"Personalizar Expandido", {"Last Event.1", each Text.Combine(List.Transform(_, Text.From)), type text})
in
#"Valores Extraídos1"

[Anonymous]

im stuck at step 4.  It gives me:

Query error ‘’. Expression.Error: We were unable to apply the operator < aos tipos Date e DateTime.
Detalhes:
Operator=<
Left=25/03/2022
Right=24/03/2022 17:14:20

[artemus]

Oh, just wrap the right side of =< with a DateTime.Date() call.

[Anonymous]

I got it! Now I'm stuck at step 6. Which table do I have to create the new column on step 6?

[Anonymous]

im not shure if i undestand correctly, but still not working. Can i call you or you call me on teams? my mail is c0628195@vale.com

[artemus]

It is possible to do this, but it is a bit complex...

Your first step is to make a date table with 1 row per date, since in the rare case there is no events for a day, you still want to generate stats.

Next, in a custom column you will need to find the last event that happened no latter than the date for that row (could be yesterday). This event will indicate if the camera is on or off.

Then you will need a column that finds the first event for that day. Then another column that determins the turn on time, which is the event time of the first event if the event is a turn on event, or the start of the day if it is a turn off event. In the case of no events for that day, you need to look at the most recent event and assign the turn on to either start of day if the last event is on, or end of day if it is off.

Once you have calculated the first turn on and last turn off time, you would calculate the total turn on or off time for that day. To do this, subtract every event on/off time in the list by the first turn on time (which can be start of day), for turn off events make the value negative. Now just add up the transformed event times and you should get the total on time (take 24 hours and subtract this value to get the off time)

[Anonymous]

Great my friend, but can you give me a hint how to start to do this? can i solve it with M language?