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.
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"
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
I don't see what the problem is with my first solution then. That one eliminated all the members that had checked out (i.e, gone through 192). Froma what I understand that is exactly what you need here. 🤔
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
Those are not the results I'm getting. When I run the report, it's:
- listing some people who have checked out
- not listing some people who have checked in and not checked out
- the checkin date/time field is stuck at 5/21 @ 2:18pm regardless of when they actually scanned
- the stationid is stuck at 52 regardless of what stationid they scanned at.
I pasted what you wrote in the Advanced Editor. Am I missing a step? I'm very new to Power BI, maybe I'm doing something wrong?
I've attached a screnshot of my whole screen so you can see what I'm looking at that shows the results from your query vs all data and I've made notes in text boxes next to them each. The results from your query shows only columns from your query and the results from all data shows only columns from my other tables. The only filter that's on is a whole page filter of the date.
Thank you for your help!
@Anonymous
I'm getting lost now. My query is stuck at 21st because the input for it (Source step) is what you initially posted and that only has data for the 21st. The query is intended to show the steps, you would have to update the Source step and read your full input data instead of the example.
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, tutoring, etc.
Cheers
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
