Forum Discussion

baekelal's avatar
baekelal
Frequent Visitor
4 years ago
Solved

Searching previous non-blank value.

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 c...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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's avatar
    baekelal
    4 years ago

    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's avatar
ddpl
Solution Sage
4 years ago

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's avatar
baekelal
Frequent Visitor
4 years ago

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:

 

NAMEActionTijdstipStatus
  3/08/2022 19:00Offline
  3/08/2022 19:30Offline
  3/08/2022 20:00Offline
Jalil BoubeddiLogging in3/08/2022 20:18Online
  3/08/2022 20:30Online
  3/08/2022 21:00Online
  3/08/2022 21:30Online
  4/08/2022 3:30Online
  4/08/2022 4:00Online
  4/08/2022 4:30Online
Jalil BoubeddiLogged off4/08/2022 4:59Offline
  4/08/2022 5:00Offline
  4/08/2022 5:30Offline
  4/08/2022 20:00Offline
  4/08/2022 20:30Offline
Jalil BoubeddiLogging in4/08/2022 20:30Online
  4/08/2022 21:00Online
  4/08/2022 21:30Online
  4/08/2022 22:00Online
  4/08/2022 22:30Online
  4/08/2022 23:30Online
  5/08/2022 3:30Online
  5/08/2022 4:00Online
  5/08/2022 4:30Online
Jalil BoubeddiLogged off5/08/2022 4:59Offline
  5/08/2022 5:00Offline

 

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's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.