Forum Discussion
If 2nd Date/Time Row, Remove Both
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
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"
- 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.
- edhans6 years ago
Community Champion
Ok, Can you provide some comprehesive data that includes your scenarios? Your original data didn't have any duplication. I don't need 10,000 rows, but 30-50 with some good examples of your true data needs would help. Please use the links below to provide data in a good format. Thanks!
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum- Anonymous6 years agoNot applicable
Here is actual data, from when our buildng was still open before COVID-19 shutdown. The check-out stationid 192 has not been actually put into place yet. That's something new we're implementing when we get to re-open. Stationid's 52 and 187 are 1st points of entry into the building and stationid 56 is an additional area within the building where members must scan again to gain entry after they've already scanned into either 52 or 187.
CHKINS checkin MEMBERS scancode STATIONS stationid STATIONS stationname 3/3/2020 7:15 33101 187 Ground Level Entry 3/3/2020 7:18 16666 52 Main Check In 3/3/2020 7:19 34103 52 Main Check In 3/3/2020 7:20 16666 56 Health & Wellness Check In 3/3/2020 7:20 34103 56 Health & Wellness Check In 3/3/2020 7:20 33101 187 Ground Level Entry 3/3/2020 7:21 28975 52 Main Check In 3/3/2020 7:21 34168 52 Main Check In 3/3/2020 7:25 32183 52 Main Check In 3/3/2020 7:26 17708 52 Main Check In 3/3/2020 7:26 32453 52 Main Check In 3/3/2020 7:26 34682 52 Main Check In 3/3/2020 7:27 32453 56 Health & Wellness Check In 3/3/2020 7:27 27881 52 Main Check In 3/3/2020 7:28 35999 52 Main Check In 3/3/2020 7:28 28497 56 Health & Wellness Check In 3/3/2020 7:29 32379 52 Main Check In 3/3/2020 7:30 27881 56 Health & Wellness Check In 3/3/2020 7:30 21150 52 Main Check In 3/3/2020 7:30 32379 56 Health & Wellness Check In 3/3/2020 7:30 35994 52 Main Check In 3/3/2020 7:31 32181 52 Main Check In 3/3/2020 7:31 21150 56 Health & Wellness Check In 3/3/2020 7:31 18741 52 Main Check In 3/3/2020 7:32 33706 52 Main Check In 3/3/2020 7:33 14817 52 Main Check In 3/3/2020 7:33 33706 56 Health & Wellness Check In 3/3/2020 7:33 29496 52 Main Check In 3/3/2020 7:34 14817 56 Health & Wellness Check In 3/3/2020 7:37 18963 52 Main Check In 3/3/2020 7:39 34209 52 Main Check In 3/3/2020 7:40 35885 52 Main Check In 3/3/2020 7:40 34209 56 Health & Wellness Check In 3/3/2020 7:41 35885 56 Health & Wellness Check In 3/3/2020 7:42 24307 52 Main Check In 3/3/2020 7:44 28945 52 Main Check In 3/3/2020 7:45 35499 52 Main Check In 3/3/2020 7:47 33143 52 Main Check In 3/3/2020 7:48 27711 52 Main Check In 3/3/2020 7:48 27226 52 Main Check In 3/3/2020 7:49 32569 52 Main Check In 3/3/2020 7:50 29720 52 Main Check In 3/3/2020 7:50 32569 56 Health & Wellness Check In 3/3/2020 7:53 32539 52 Main Check In 3/3/2020 7:55 34684 52 Main Check In 3/3/2020 7:56 33221 52 Main Check In 3/3/2020 7:56 34845 52 Main Check In 3/3/2020 7:56 23489 52 Main Check In 3/3/2020 7:57 23489 56 Health & Wellness Check In 3/3/2020 7:58 33812 52 Main Check In
- Anonymous6 years agoNot applicable
Thanks for this! Some weirdness, though (and both of your solutions provided the exact same results):
- Not all memid’s were removed on scan at check-out stationid 192. See memid 76795. But memid 98033 was removed.
- The checkin date/time is different in the Query than what it actually is. The Table CHKINS is correct.
- Same for the stationid – Query is not correct, but table CHKINS is correct.
- Memid 104958 is not picking up any scan in the Query, but if I look at the raw data, is clearly scanning.
Also, and I apologize for not mentioning this earlier:
- Stationid 187 is an alternate initial entry to the building and should be treated the same as stationid 52.
- Stationid 56 is a secondary scan-in area of the building and should not be treated as a check-out.
In the data I’ve included below, the table names are in all caps in the heading.
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