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.
@Anonymous - here is the code that Power BI generates when you connect to a SQL server. This is connecting to a sales table in the AdventureWorks2017 database on my local machine:
let
Source = Sql.Database("localhost", "AdventureWorks2017"),
Sales_vIndividualCustomer = Source{[Schema="Sales",Item="vIndividualCustomer"]}[Data]
in
Sales_vIndividualCustomer
This is the sample code I provided to do what you requested:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZRLSwMxFIX/Sph1pXmcTBK3IiooLl3ULqoGKo4jTFvBf28y1RIVy72ZWWQ25577Sr7FojFzM9dSS+FOlW1mjTFKqvRX3qXzYnjb9U/iOr7HTpz32+GjWc5+BrVJppWyMv2tTsfN6rkXZ+v4+CKu+j9yn73b9NHkIZcEJQ1JrmXpno/LuOq2a3G/k1K34i52XR83m+MGh3zVBswZ6izWPjhL61Lti2w9TY5yQzU9jfdCK09cQk6hnJPE8trRHZbhbtB6TZO70r2m+WygnfeKli83bWwIgS7/3rwKoz6+PsRhH3Fyu9v+E4Lgajsq3xQx5xiijaN1ZWQ5s4oSR4NDvmqDtAbQClZfN5y2ZGMLzNAmCF+MhBgSiqtEC7GyeKlHQ1ADffCgDx70wYM+pkIfU6GPGuiDB33woI+p0AcP+uBBHzzogwd9TIU+eNAHD/rgQ/93CBv64EMfPOhjKvQxFfrgQR886IMPffChDz70wYL+8hM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CHKINS checkin" = _t, #"MEMBERS scancode" = _t, #"STATIONS stationid" = _t, #"STATIONS stationname" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CHKINS checkin", type datetime}, {"MEMBERS scancode", Int64.Type}, {"STATIONS stationid", Int64.Type}, {"STATIONS stationname", type text}}),
#"Added Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([CHKINS checkin]), type date),
#"Grouped Rows" = Table.Group(#"Added Date", {"Date", "MEMBERS scancode"}, {{"All Rows", each _, type table [CHKINS checkin=datetime, MEMBERS scancode=number, STATIONS stationid=number, STATIONS stationname=text, Date=date]}}),
#"Added Checked Out Validation" =
Table.AddColumn(
#"Grouped Rows",
"Checked Out",
each (
List.ContainsAny([All Rows][STATIONS stationid], {187,52})
and List.Contains([All Rows][STATIONS stationid], 192)
)
, type logical),
#"Filtered out Checked Out IDs" = Table.SelectRows(#"Added Checked Out Validation", each ([Checked Out] = false)),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Filtered out Checked Out IDs", "All Rows", {"CHKINS checkin", "STATIONS stationid", "STATIONS stationname"}, {"CHKINS checkin", "STATIONS stationid", "STATIONS stationname"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"CHKINS checkin", "MEMBERS scancode", "STATIONS stationid", "STATIONS stationname"})
in
#"Removed Other Columns"You have to make the following changes to your M code after you've connected to the table in the SQL Server:
- Open the advanced editor. Your first 2-3 lines will be similar to those below, but with different server, database, and table names
- Get rid of everything after line 3 below. Yours may have more lines to get to the actual table depending on how you connected
- Copy the code above starting with the "#"Added Date" step through the end.
- On line 3 below, add a comma after the [Data] step. Shown with a 1 below.
- On line 4 below, you need to change the table reference to reference thte table name in step 3
Unless you gave us all of the exact field names (and they have changed each time you did a copy of data from Excel), and by exact, I mean the exact number of characters, the case of the letters, etc., my code will fail, and you'll have to edit all of those in rows 5-17, and if you have other columns you need retained, then the expansion on row 16 needs to be edited. Additionally, the comparsons done on rows 7-14 assume your station is a whole number. If it is actually a text string with the numbers 192, 56, etc, then the comparsions will have to be redone as strings.
At this point, there is nothing more I can do to help via these forums. You have said you are a Power BI beginner, and I fully appreciate that. But you have asked for help in something that I would consider probably an intermediate process, so this isn't like helping with a complex Excel formula. To do anything more, we'd have to do a remote screen-share to fix, and that is now a consulting issue that I charge for, not free support in the forums to assist with tricky Power BI issues. You can PM me if you wish to pursue that, or you can find a Power BI consultant locally to come into your office to help getting it configured.
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.
