Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

If 2nd Date/Time Row, Remove Both

I'm pulling data from SQL for facility check-ins.  I want only a list of people who are still in the building.  In the attached screen shot, stationid 52 is a check-in station and stationid 192 is a ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

OK, I typed the relevent columns directly into Excel and pasted that here...

chkinidmemidcheckinstationid
5438177980335/21/2020 2:17:49 PM52
54381781307325/21/2020 2:17:56 PM52
54381791348065/21/2020 2:18:06 PM52
5438180767955/21/2020 2:18:14 PM52
54381811030555/21/2020 2:18:20 PM52
5438182976405/21/2020 2:18:26 PM52
54381831130625/21/2020 2:18:35 PM52
5438184980335/21/2020 2:49:17 PM192
  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Is this what you need?

    I didn't actually compare the station IDs. I just removed them if there were more than one present 

    You might want to add additional Group By columns (Days, employee codes or whatever) if your data is a bit more complex. See the M code below.

     

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLCsMwDAWvErwORF9L8h0K3Yfc/xqRU7qp1ZXgMYOkd55NhR3N2t7CgTmnHoQHAcFGA21IbO/XjKld+5f3DJDBmFZBeyHEI4hD/xV8QCE4ZGDdQlcepeBxLgAGLYScqzAvD+sCBV8dNKvB/LkvL/tgLQT506lEtvThMVK4bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [chkinid = _t, memid = _t, checkin = _t, stationid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"chkinid", Int64.Type}, {"memid", Int64.Type}, {"checkin", type datetime}, {"stationid", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"memid"}, {{"All Rows", each _, type table [chkinid=number, memid=number, checkin=datetime, stationid=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Keep", each Table.RowCount([All Rows]) < 2, type logical),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = true)),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Filtered Rows", "All Rows", {"chkinid", "checkin", "stationid"}, {"chkinid", "checkin", "stationid"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"checkin", "memid", "chkinid", "stationid"})
in
    #"Removed Other Columns"

     

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Anonymous 

      Try this:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLCsMwDAWvErwORF9L8h0K3Yfc/xqRU7qp1ZXgMYOkd55NhR3N2t7CgTmnHoQHAcFGA21IbO/XjKld+5f3DJDBmFZBeyHEI4hD/xV8QCE4ZGDdQlcepeBxLgAGLYScqzAvD+sCBV8dNKvB/LkvL/tgLQT506lEtvThMVK4bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [chkinid = _t, memid = _t, checkin = _t, stationid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"chkinid", Int64.Type}, {"memid", Int64.Type}, {"checkin", type text}, {"stationid", Int64.Type}}),
    whos_checkedout_ = Table.SelectRows(#"Changed Type",each [stationid] = 192 )[memid],
    final_ = Table.SelectRows(#"Changed Type",each not List.Contains(whos_checkedout_,[memid]) )
in
    final_

       Please mark the question solved when done and consider giving kudos if posts are helpful.

      Contact me privately for support with any larger-scale BI needs

      Cheers 

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        FYI Anonymous - neither solution above will work if you have the same memid check in on a different day. The below will handle it if it does. This speifically checks for the 52 and 192 codes as well, so code 171 would not cause this to get deleted for example.


        But if you have multiple checkins a day, this will not work either, or a checkin at 11:50pm and a checkout at 12:03am the next day. We'd need to see a more comprehensive data set to see how to account for other possibilities, including additional columns if necessary.

         

        1) In Power Query, select New Source, then Blank Query
        2) On the Home ribbon, select "Advanced Editor" button
        3) Remove everything you see, then paste the M code I've given you in that box.
        4) Press Done

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLCsMwDAWvErwORF9L8h0K3Yfc/xqRU7qp1ZXgMYOkd55NhR3N2t7CgTmnHoQHAcFGA21IbO/XjKld+5f3DJDBmFZBeyHEI4hD/xV8QCE4ZGDdQlcepeBxLgAGLYScqzAvD+sCBV8dNKvB/LkvL/tgLQT506lEtvThMVK4bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [chkinid = _t, memid = _t, checkin = _t, stationid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"chkinid", Int64.Type}, {"memid", Int64.Type}, {"checkin", type datetime}, {"stationid", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date Only", each DateTime.Date([checkin]), type date),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"memid", "Date Only"}, {{"All Rows", each _, type table [chkinid=number, memid=number, checkin=datetime, stationid=number, Date Only=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Remove", each List.ContainsAll([All Rows][stationid],{52,192})),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Remove] = false)),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Filtered Rows", "All Rows", {"chkinid", "checkin", "stationid"}, {"chkinid", "checkin", "stationid"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"memid", "chkinid", "checkin", "stationid"})
in
    #"Removed Other Columns"