Yes, there is an easy way in fact.

Add 2 index columns: one starting from 0 and the other starting from 1.

Merge the query with itself, with index0 as the key on one side, and the index1 on the other side.
Now, if you expand the Date-Time from the merge, you will have Entry and exit in the same row, so you can perform your calculations easier.

Thank you for your response. I think that solution more or less does the trick. The problem I'm running into is I have Person A, Person B, etc. I can't have person B information pulling onto a row with Person A, so it's almost like the data needs to be grouped somehow. Does that make sense?

Data seems to be in pairs, so if data is consistent , the offset should work.

There can be other ways, but more complicated: create a function that filters the table by person, keep the first date after the entry date-time.

Hi again, thanks for your response. While the data does appear to be in pairs, the problem arises when looking at current information - say for example, somebody who is currently on site. I'm sorry I didn't have that in my sample file because it's really the kicker. They have an entry scan but no exit scan. The merge query then pulls the entry scan from the next person on the list, which is incorrect behaviour. I need to allow for no Exit scan essentially. If there's no exit then leave it blank and skip to the next row somehow.

Hi Adam,

try this query:

Wow, I think this may work, though I'm having a difficult time trying to understand the add tbl functionality in PQ.

I'm trying to get this to work with my actual file and it's returning all errors in the Exit column. Would you be able to look at the code below and see if it makes sense why it's not working properly? I get the feeling it's got something to do with the red word "SOURCE" in the line

FilterTable(Source,_[Name],_[Company],_[FieldDateTime])),

, but I'm hoping you can correct me. Some of the column names are a little different, and I've wiped out (...) some confidential data.

Your help is incredibly appreciated!

EDIT: I think I fixed the problem w/ a workaround. My "source" is a SharePoint folder, which seems to be causing a problem. To resolve the issue, I created a reference of that query, and your code worked with the reference source. I'm not sure this is the best way to handle it, but it appears to be working, and that's great.

EDIT 2: Scratch that. I'm getting some Sharepoint request failed errors while trying to load. Maybe I'm pushing it too hard.

let
Source = SharePoint.Files("xxxxxxxxxxxxxxxxxxxxxxxxxxxx", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "xxxxxxxxxxxxxxxxxxxxxxxxxxxx")),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Name", each Text.BeforeDelimiter(_, " "), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Name", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Name]) in each [Name] = latest or Date.IsInPreviousNDays([Name], 30)),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

....


ChangedType = Table.TransformColumnTypes( #"Sorted Rows",{{"FieldDateTime", type datetime}}),
FilteredRows = Table.SelectRows(ChangedType, each ([Transit Direction] = "Entry")),

FilterTable = (tbl as table, Person as text, Company as text, EntryDate as datetime)=>
let
Filtered = Table.SelectRows(tbl, each ([Name] = Person and [Company] = Company and [FieldDateTime] > EntryDate and [Transit Direction]="Exit")),
Sorted = Table.Sort(Filtered,{{"FieldDateTime", Order.Ascending}})
in Table.FirstN(Sorted,1),

AddedCustom = Table.AddColumn(FilteredRows, "Exit", each FilterTable(Source,_[Name],_[Company],_[FieldDateTime])),
#"Expanded Exit" = Table.ExpandTableColumn(AddedCustom, "Exit", {"FieldDateTime", "Transit Direction"}, {"Exit.FieldDateTime", "Exit.Transit Direction"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Exit",{{"Exit.FieldDateTime", type datetime}})

in
#"Changed Type2"

glad to hear you managed to make it work.

The function FilterTable can be moved as an external function, if needed.

The function FilterTable can be moved as an external function, if needed.

Any chance you can help be understand that a little better? I'm a little self-taught in Power Query, and I'm unsure about the FilterTable. Also, as per EDIT 2 above, I'm second guessing my source work around is going to work at all. 

Move this code in a new query, named FilterTable:

(tbl as table, Person as text, Company as text, EntryDate as datetime)=>
let
Filtered = Table.SelectRows(tbl, each ([Name] = Person and [Company] = Company and [FieldDateTime] > EntryDate and [Transit Direction]="Exit")),
Sorted = Table.Sort(Filtered,{{"FieldDateTime", Order.Ascending}})
in Table.FirstN(Sorted,1)

There should be nothing between FilteredRows and AddedCustom steps.
This is a function. Before, it was still a function, but incorporated into the main query, now it is an independent function.

Thanks for explaining the FilterTable - I was able to get that created as a separate query, without error. 

I've tried pasting the remaining code again to my original SharePoint query, and still seem to be getting the error from the Source - Are you aware of what might be causing this problem?

I think I solved the problem of that row by changing "Source" with #"Removed Other Columns1", essentially pointing to a step later in the query once all the data was in the right place. Seems like a bit circular, but it appears to be working.

Also, when loading this to a table it is incredibly slow and seems like it may not be usable, as it's loading one row at a time, and after 30 days there will be thousands of records. Would using a FilterTable like this typically take a good deal of time to run through?

If you look at the example i sent, Source table is using Table.Buffer, I did not saw it in your query. The table that needs to be sent to FilterTable function needs to be loaded into memory, otherwise FilterTable will have to READ and process the sharepoint folder every time it is called - at every row, the function will not memorize what you sent previously.

So it's wise to load it into memory once before AddCustom and pass it from memory at each row, will make a huge difference.

Thank you, thank you and thank you. You've been a great help.

Hello Catalin, I was looking over your previous code, and I still think this is going to work, but it's providing something just a tiny bit different that what I need.

I've attached a file using your code. In the file you'll see in the left table there is an Exit Scan from 5:01 AM highlighted in yellow that does not appear on the right. It should be on the right side in a row above the 6:38 record with a blank beside it to the left for no entry.

If you change the AM to PM and refresh query, it appears in the proper spot. Your help is greatly appreciated!

https://drive.google.com/file/d/1Rpfu7NxGUlM_6kjhEAS7AnLYpbeZCRi4/view?usp=sharing

How can that person B have 2 Entry and only 1 Exit ? Sounds like magic...

More, that Exit is before both entries.

The function is looking for an Exit AFTER the Entry time, and this is the right way. Anomalies like this cannot be handled, they are data source errors that needs to be fixed manually.