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 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.

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.

4 Replies

  • ddpl's avatar
    ddpl
    Icon for Solution Sage rankSolution 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
        Icon for Community Support rankCommunity 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.

  • baekelal's avatar
    baekelal
    Frequent Visitor
    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.