Thanks for your response, I totally get what you're saying. Perhaps you'll humour me momentarily with a thought. In the dummy file provided, we're looking at a very limited scope. In our working data, the collection begins at January 1 2021. 

I can see how if there's no earlier entry scan it doesn't make sense to have an exit scan. It would be nice if it didn't eliminate the data, though. Unfortunately, as we lose sight of that scan. There are some instances on our site where a person may enter via the passenger seat of a car and hence not have an entry scan, but they may scan out later that day. At least we'll know they were there.

At the very least, this seems to capture Exit -> Entry, which should be what we're looking for.

Edit

As a second point, I'm replicating the query in Power BI with our actual source material. The columns are all exactly the same, yet when I reach the step "AddedCustom" ( = Table.AddColumn(FilteredRows, "Exit", each FilterTable(Source,_[Name],_[Company],_[Field Date Time])) ), the new column "Exit" produces all Errors instead of Table. The error reads

Expression.Error: The specified sort criteria is invalid.
Details:

I've got everything exactly the same as the Excel file to the best of my knowledge. Are you aware of any difference in how Power BI PQ works vs Excel?

 

As a follow up to my last post, I've tried recreating in Excel Power Query with our actual data, and I'm getting the same behaviour as Power BI where the [Exit] column populates with Errors. The only thing I can imagine is the fact that there are too many rows of data, but I'm open to suggestions.

Here is my code, with sensitive information starred out

let
Source = Table.Buffer(SharePoint.Files("***", [ApiVersion = 15])),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "")),
#"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 Hidden Files1" = Table.SelectRows(#"Changed Type", 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"))),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Table Column1",{{"FullName1", "Name"}, {"Textbox227", "Company"}, {"Textbox81", "Trade"}, {"Textbox32", "Status"}, {"DateOnly", "Date"}, {"TimeOnly", "Time"}, {"Description", "Gate"}, {"TransitDirection1", "Direction"}, {"FieldDateTime", "Field Date Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Time", type time}, {"Field Date Time", type datetime}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each ([Time] <> null)),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows2", "", each if [Gate] = "" then "Entry" else if [Gate] = "" then "Exit" else if [Gate] = "" then "Entry" else if [Gate] = "" then "Exit" else if [Gate] = "" then "Entry" else if [Gate] = "" then "Exit" else if [Gate] = "" then "Entry" else if [Gate] = "" then "Exit" else null),
#"Merged Columns" = Table.CombineColumns(#"Added Conditional Column",{"", "Direction"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Direction"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Status", "CardNumber", "Source", "ConditionName", "Date", "Time", "Gate"}),
#"" = Table.SelectRows(#"Removed Columns", each ([Company] <> "")),

FilteredRows = Table.SelectRows(#"***", each ([Direction] = "Entry")),

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

AddedCustom = Table.AddColumn(FilteredRows, "Exit", each FilterTable(Source,_[Name],_[SBIID1],_[Trade],_[Company],_[Field Date Time])),
#"Expanded Exit" = Table.ExpandTableColumn(AddedCustom, "Exit", {"Field Date Time", "Direction"}, {"Exit.Field Date Time", "Exit.Direction"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Exit",{{"Exit.Field Date Time", type datetime}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Direction", "Exit.Direction"})

in
#"Removed Columns1"