If 2nd Date/Time Row, Remove Both

Anonymous I somehow missed your earlier post this week with more data. I'll get back to this hopefully tonight. Perhaps AlB will solve it before then, but if not, just wanted you to know I didn't bail. I just missed the notification with more data. 😁

AlB 

The original data had the 21st because that's what day it was then.  I need this to update real-time.  How do I update the source?  Like I said... brand spankin' new to Power BI, so I really don't know what I'm doing yet.  I just need this to work before Monday.

Thank you!

Ok, Anonymous, see if this works. Here is what I did:

1. Duplicated your data and changed the date to the 4th so I had 2 days to play with to ensure it handled says right.
2. Added a days only field (ignoring time)
3. Grouped by day and ID
4. filtered for any rows that had (187 or 52) and 192 and removed them
5. kept the rest. I ignored any 56 records.

The PBIX file is here.

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"

I'm sorry!  Your query still only shows data from the past, but I need this report to update with data real-time.  The purpose is to be able to pull this report at any time to see who is in the building at that time.

Can you provide data that correlates to that? What my data returns is anyone that checked in, but didn't check out.

edhans 

Here's some data from today.  The query, for it to work, can't pull from a static set of data.  It needs to pull from our SQL database.

The query I am giving you is sample code. I cannot pull from your SQL server. Did you use the steps in my query, but point it to your data source, not the static data? Giving me static data for today will still not pull from the SQL server. 

edhans 

How do I do that?  I'm very new to Power BI.

Thanks for your help!

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. 

Hi , Anonymous 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.

It will help other community members easily find the solution when they get the similar issue.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.