Forum Discussion
If 2nd Date/Time Row, Remove Both
OK, I typed the relevent columns directly into Excel and pasted that here...
| chkinid | memid | checkin | stationid |
| 5438177 | 98033 | 5/21/2020 2:17:49 PM | 52 |
| 5438178 | 130732 | 5/21/2020 2:17:56 PM | 52 |
| 5438179 | 134806 | 5/21/2020 2:18:06 PM | 52 |
| 5438180 | 76795 | 5/21/2020 2:18:14 PM | 52 |
| 5438181 | 103055 | 5/21/2020 2:18:20 PM | 52 |
| 5438182 | 97640 | 5/21/2020 2:18:26 PM | 52 |
| 5438183 | 113062 | 5/21/2020 2:18:35 PM | 52 |
| 5438184 | 98033 | 5/21/2020 2:49:17 PM | 192 |
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"
- AlB6 years ago
Community Champion
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
- edhans6 years ago
Community Champion
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 Donelet 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"- Anonymous6 years agoNot applicable
edhans Some memid's will check in and out multiple times per day. There shouldn't be anyone checking in one day and checking out the next.
- Anonymous6 years agoNot applicable
Thanks for this... some weirdness, though (and both methods got the exact same results):
- 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.
- The checkin date from the query doesn't match when they actually scanned in.
- The stationid from the query doesn't match the stationid that they actually scanned in on.
- 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 checkin QUERY1 checkin QUERY1 chkinid QUERY1 memid STATIONS stationid QUERY1 stationid STATIONS stationname 5/25/2020 7:52:39 AM 5/21/2020 2:18:26 PM 5438182 97640 187 52 Ground Level Entry 5/25/2020 12:08:48 PM 5/21/2020 2:18:06 PM 5438179 134806 52 52 Main Check In 5/25/2020 12:08:56 PM 5/21/2020 2:18:14 PM 5438180 76795 52 52 Main Check In 5/25/2020 12:09:09 PM 5/21/2020 2:18:20 PM 5438181 103055 52 52 Main Check In 5/25/2020 12:59:05 PM 5/21/2020 2:18:06 PM 5438179 134806 56 52 Health & Wellness Check In 5/25/2020 1:12:59 PM 5/21/2020 2:18:14 PM 5438180 76795 192 52 Member Check-Out