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 check-out station.  If memid has an entry for the stationid 192 check-out staion, I want to filter out both rows for stationid 52 and stationid 192, leaving only the rows for memid who have checked-in and no rows for memid who have checked-out.  You can see in this screen shot that memid 98033 has a row for both stationid 52 and 192.  I want both rows for memid 98033 filtered out.

Need Help

28 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I keep getting "invalid HTML" errors on the headings when I try to paste the data into the table.

    • 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
        Community 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"