Calculating time between two rows of datetime stamps|Power Query|Excel Forum|My Online Training Hub

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?

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.

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"

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. 

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?

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

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"