Log In
Register
Home
December 18, 2018
Offline
Hello, I have gate scans being provided to me on a daily basis and I need to calculate time spent on and offsite per person. Each record contains a date stamp of either a 'entry' or 'exit'. It would be really easy if they were on the same line to then just subtract one from the other, but unfortunately the data can't be sent to me that way.
Is there any easy way to subtract one line from the previous line given the name and company match, or to bring the "Exit" and Time stamp up to the previous row, using Power Query?
Thanks for your help!
See Answer
November 8, 2013
Offline
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.
December 18, 2018
Offline
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?
November 8, 2013
Offline
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.
December 18, 2018
Offline
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.
November 8, 2013
Offline
Hi Adam,
try this query:
let
Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="AdamS"]}[Content]),
ChangedType = Table.TransformColumnTypes(Source,{{"Field Date Time", type datetime}}),
FilteredRows = Table.SelectRows(ChangedType, each ([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 [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],_[Company],_[Field Date Time])),
#"Expanded Exit" = Table.ExpandTableColumn(AddedCustom, "Exit", {"Field Date Time", "Direction"}, {"Exit.Field Date Time", "Exit.Direction"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Exit",{{"Exit.Field Date Time", type datetime}})
in
#"Changed Type"
December 18, 2018
Offline
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"
November 8, 2013
Offline
glad to hear you managed to make it work.
The function FilterTable can be moved as an external function, if needed.
December 18, 2018
Offline
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.
November 8, 2013
Offline
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.
December 18, 2018
Offline
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?
November 8, 2013
Offline
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.
Answers Post
December 18, 2018
Offline
Thank you, thank you and thank you. You've been a great help.
December 18, 2018
Offline
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!
November 8, 2013
Offline
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.
December 18, 2018
Offline
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:
[List]
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?
December 18, 2018
Offline
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"
1 Guest(s)
