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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

AlB and edhans 

Thanks for this... some weirdness, though (and both methods got the exact same results):

  1. It's not removing all people who have scanned at check-out stationid 192 (see memid 76795).  It did remove memid 98033 who checked out.
  2. The checkin date from the query doesn't match when they actually scanned in.
  3. The stationid from the query doesn't match the stationid that they actually scanned in on.
  4. It's also not picking up any scans from memid 104958.

In the below data, I've included checkin and stationid from both the query and the table for comparision.  The table names are in all caps in each column heading.  

Also - I apologize for not mentioning this earlier, but stationid 56 is a secondary check in station within the facility and should not be counted as a check out when it occurs after initially scanning at stationid 52 upon entry.  Stationid 187 is an alternate initial entry to the building and should be treated the same as stationid 52.  

 

CHKINS checkinQUERY1 checkinQUERY1 chkinidQUERY1 memidSTATIONS stationidQUERY1 stationidSTATIONS stationname
5/25/2020 7:52:39 AM5/21/2020 2:18:26 PM54381829764018752Ground Level Entry
5/25/2020 12:08:48 PM5/21/2020 2:18:06 PM54381791348065252Main Check In
5/25/2020 12:08:56 PM5/21/2020 2:18:14 PM5438180767955252Main Check In
5/25/2020 12:09:09 PM5/21/2020 2:18:20 PM54381811030555252Main Check In
5/25/2020 12:59:05 PM5/21/2020 2:18:06 PM54381791348065652Health & Wellness Check In
5/25/2020 1:12:59 PM5/21/2020 2:18:14 PM54381807679519252Member Check-Out