Searching previous non-blank value.

[baekelal]

Hi all,

I'm stuck for a while on this situation:

See here a sample off my data:

I have for all employees a column with the timestamp when they logged in or logged out.

Now i want to create a 3rdcustomn column with an "online" status.

So in this example i should see "Online" for each row between 20:18:36 and 04:59:11. Before and after that period it should be filled with "offline".

The formula should look for the previous rows till it's not blanc. If it is not blank is should report "online" when text "Logging in" is found and "Offline" when "Logged off" is found. I added an index column but also no luck with that.

Anyone suggestions?

Thanks in advace.

[v-kkf-msft · Accepted solution]

Hi @baekelal ,

Please open a blank query and paste the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZHBCsIwEER/JeRc6GaThdajR/EPSg9K2hAozcn/twhWSTNBDzm9zOzuzDBopZvXsy11LROzMv2JSI8NYBYxprfuclvios7pcZ+8j9unawohrkHFNVeYDrvhSQZvuLFM53ZmMXK54zeylbMmr9I8ZwLpgZfgMYKX+yRbZLX19tRLioLbIVn3U7LMFR1XdIdKBLcluC35ty3BbUnW1vgE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, Action = _t, Tijdstip = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"Action", type text}, {"Tijdstip", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Action] = "Logging in" then "Online" else if [Action] = "Logged off" then "Offline" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Status"}),
    #"Transform Columns" = Table.TransformColumns( #"Filled Down", {{"Status", each if _ = null then "Offline" else _, type text}
    })
in
    #"Transform Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[baekelal · Accepted solution]

Hi All,

I searched a bit more myself and found a solution. This is what i came up with:

Agentname =

var naam = "Agentname"

VAR lastlogin = if(Nice[AGENT_NAME]=naam && Nice[STATE_NAME]="Logging in",Nice[Datum],

CALCULATE(MAX(Nice[Datum]),ALL(Nice),Nice[AGENT_NAME]= naam && Nice[STATE_NAME] = "Logging in" && 'Nice'[Datum]<= EARLIER(Nice[Datum])))

VAR lastlogoff = if(Nice[AGENT_NAME]=naam && Nice[STATE_NAME]="Logged off",Nice[Datum],CALCULATE(

    MAX(Nice[Datum]),

    ALL(Nice),Nice[AGENT_NAME]= naam && Nice[STATE_NAME] = "Logged off" && 'Nice'[Datum]<= EARLIER(Nice[Datum])))

return

if(lastlogin = BLANK() || lastlogin = BLANK(),0,

if(lastlogin>= lastlogoff,1,0))

Your supposed solution will probably work but i can further with my calculated column (i made one for eacht agent). Thanks alot to help searching for a solution.

[ddpl]

@baekelal ,

In Power Query, First replace blank cells of Log-In/Log-Off column with null.

Then do Fill Down, either from Transform menu or right click on column.

Replace Log-In value with Online and Log-Off and null values with Offline.

Voila, You got what you want.

[baekelal]

Hi, 

this is not what i want. It depends on the last action off that person what follows. I made a better view off what i want:

NAME	Action	Tijdstip	Status
		3/08/2022 19:00	Offline
		3/08/2022 19:30	Offline
		3/08/2022 20:00	Offline
Jalil Boubeddi	Logging in	3/08/2022 20:18	Online
		3/08/2022 20:30	Online
		3/08/2022 21:00	Online
		3/08/2022 21:30	Online
		4/08/2022 3:30	Online
		4/08/2022 4:00	Online
		4/08/2022 4:30	Online
Jalil Boubeddi	Logged off	4/08/2022 4:59	Offline
		4/08/2022 5:00	Offline
		4/08/2022 5:30	Offline
		4/08/2022 20:00	Offline
		4/08/2022 20:30	Offline
Jalil Boubeddi	Logging in	4/08/2022 20:30	Online
		4/08/2022 21:00	Online
		4/08/2022 21:30	Online
		4/08/2022 22:00	Online
		4/08/2022 22:30	Online
		4/08/2022 23:30	Online
		5/08/2022 3:30	Online
		5/08/2022 4:00	Online
		5/08/2022 4:30	Online
Jalil Boubeddi	Logged off	5/08/2022 4:59	Offline
		5/08/2022 5:00	Offline

The column "Status" should be calculated on the column "Action":

- If column Action = "Logging in" status should be "Online"

- If column Action = "Logged off" status should be "Offline

- If column Action = blanc then te previous last action should remain.

See the example.

Any help is welcome.

[v-kkf-msft · Accepted solution]

Hi @baekelal ,

Please open a blank query and paste the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZHBCsIwEER/JeRc6GaThdajR/EPSg9K2hAozcn/twhWSTNBDzm9zOzuzDBopZvXsy11LROzMv2JSI8NYBYxprfuclvios7pcZ+8j9unawohrkHFNVeYDrvhSQZvuLFM53ZmMXK54zeylbMmr9I8ZwLpgZfgMYKX+yRbZLX19tRLioLbIVn3U7LMFR1XdIdKBLcluC35ty3BbUnW1vgE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, Action = _t, Tijdstip = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"Action", type text}, {"Tijdstip", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Action] = "Logging in" then "Online" else if [Action] = "Logged off" then "Offline" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Status"}),
    #"Transform Columns" = Table.TransformColumns( #"Filled Down", {{"Status", each if _ = null then "Offline" else _, type text}
    })
in
    #"Transform Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.