Excel / powerquery formula to compare dates/time when other value is matching.

B

BadTweety

New Member
Joined
Jan 2, 2017
Messages
8
Hi all,

I have a list of shipping numbers with piece numbers and date/time of first scan. Now i want to know when there is more then 1 hour between different piece numbers of the same shipping number (partial arrival).

As i have more then 100k rows i would like to find a formula in PowerQuery to reduce the stress on excel but all ideas are welcome!

the table should have following outcome.

Shipping numberpiece numberfirst scanPartial arrival?
123456789222/06/2019 13:00Yes
456789123121/06/2019 10:00no
123456789422/06/2019 13:50Yes
123456789322/06/2019 14:10Yes
456789123221/06/2019 10:50no
123456789122/06/2019 14:30Yes
987654321222/06/2019 12:10Yes
987654321323/06/2019 12:30Yes
987654321122/06/2019 12:50Yes

<tbody>
</tbody>

I was thinking to combine a min, max function combined with countifs but i cant figure it out :(
in addition i am limited to excel 2013 and the datedif function doesnt seem to be included in this version.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Here's a piece of M-code that groups rows by Shipping number and extracts the min & max time stamps + calculates a column to check if the duration between the scans is more than an hour:
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipping number", type text}, {"piece number", Int64.Type}, {"first scan", type datetime}, {"Partial arrival?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Shipping number"}, {{"First Scan", each List.Min([first scan]), type datetime}, {"Last Scan", each List.Max([first scan]), type datetime}, {"All Rows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hour+ Apart", each [Last Scan]-[First Scan]>#duration(0,1,0,0),type logical)
in
    #"Added Custom"
You might have to adjust the code a little bit but I'm sure you can take it from here.
 
Upvote 0
is that what you want?

Shipping numberpiece numberfirst scanShipping numberpiece numberfirst scanY/N
123456789​
2​
22/06/2019 13:00​
123456789​
2​
22/06/2019 13:00​
Yes
456789123​
1​
21/06/2019 10:00​
456789123​
1​
21/06/2019 10:00​
No
123456789​
4​
22/06/2019 13:50​
123456789​
4​
22/06/2019 13:50​
Yes
123456789​
3​
22/06/2019 14:10​
123456789​
3​
22/06/2019 14:10​
Yes
456789123​
2​
21/06/2019 10:50​
456789123​
2​
21/06/2019 10:50​
No
123456789​
1​
22/06/2019 14:30​
123456789​
1​
22/06/2019 14:30​
Yes
987654321​
2​
22/06/2019 12:10​
987654321​
2​
22/06/2019 12:10​
Yes
987654321​
3​
23/06/2019 12:30​
987654321​
3​
23/06/2019 12:30​
Yes
987654321​
1​
22/06/2019 12:50​
987654321​
1​
22/06/2019 12:50​
Yes

Let me know if you don't manage with the post #2
 
Upvote 0
thank you so much !

Grouped rows where the key to success.
its not showing which piece is first/last but in my case i dont need that information.

with some minor changes i just made my daily work a lot faster.

(y)(y)
 
Upvote 0
BadTweety said:
thank you so much !

Grouped rows where the key to success.
its not showing which piece is first/last but in my case i dont need that information.

with some minor changes i just made my daily work a lot faster.

(y)(y)
Click to expand...

Worked with post nr 2 btw ! ;)
 
Upvote 0
sandy666 said:
is that what you want?

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Shipping number[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]piece number[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]first scan[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Shipping number[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]piece number[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]first scan[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Y/N[/COLOR]
123456789​
2​
22/06/2019 13:00​
123456789​
2​
22/06/2019 13:00​
Yes
456789123​
1​
21/06/2019 10:00​
456789123​
1​
21/06/2019 10:00​
No
123456789​
4​
22/06/2019 13:50​
123456789​
4​
22/06/2019 13:50​
Yes
123456789​
3​
22/06/2019 14:10​
123456789​
3​
22/06/2019 14:10​
Yes
456789123​
2​
21/06/2019 10:50​
456789123​
2​
21/06/2019 10:50​
No
123456789​
1​
22/06/2019 14:30​
123456789​
1​
22/06/2019 14:30​
Yes
987654321​
2​
22/06/2019 12:10​
987654321​
2​
22/06/2019 12:10​
Yes
987654321​
3​
23/06/2019 12:30​
987654321​
3​
23/06/2019 12:30​
Yes
987654321​
1​
22/06/2019 12:50​
987654321​
1​
22/06/2019 12:50​
Yes

<tbody>
</tbody>


Let me know if you don't manage with the post #2
Click to expand...

I am curious tough, what was your solution ?
 
Upvote 0
post#2 is similar to my code, I just finished it

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

// Table1_2
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Shipping number"}, {{"Max", each List.Max([first scan]), type datetime}, {"Min", each List.Min([first scan]), type datetime}, {"Count", each _, type table}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Max] - [Min], type duration),
    Condition = Table.AddColumn(Subtract, "Y/N", each if [Subtraction] > #duration(0, 1, 0, 0) then "Yes" else "No"),
    ROC = Table.SelectColumns(Condition,{"Shipping number", "Y/N"})
in
    ROC

// Result
let
    Source = Table.NestedJoin(Table1_2,{"Shipping number"},Table1,{"Shipping number"},"Table3 (2)",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table3 (2)", {"piece number", "first scan", "Index"}, {"piece number", "first scan", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Shipping number", "piece number", "first scan", "Y/N"}),
    ROC = Table.SelectColumns(Reorder,{"Shipping number", "piece number", "first scan", "Y/N"}),
    Type = Table.TransformColumnTypes(ROC,{{"first scan", type datetime}})
in
    Type[/SIZE]
 
Last edited:
Upvote 0
sandy666 said:
post#2 is similar to my code, I just finished it

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

// Table1_2
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Shipping number"}, {{"Max", each List.Max([first scan]), type datetime}, {"Min", each List.Min([first scan]), type datetime}, {"Count", each _, type table}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Max] - [Min], type duration),
    Condition = Table.AddColumn(Subtract, "Y/N", each if [Subtraction] > #duration(0, 1, 0, 0) then "Yes" else "No"),
    ROC = Table.SelectColumns(Condition,{"Shipping number", "Y/N"})
in
    ROC

// Result
let
    Source = Table.NestedJoin(Table1_2,{"Shipping number"},Table1,{"Shipping number"},"Table3 (2)",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table3 (2)", {"piece number", "first scan", "Index"}, {"piece number", "first scan", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Shipping number", "piece number", "first scan", "Y/N"}),
    ROC = Table.SelectColumns(Reorder,{"Shipping number", "piece number", "first scan", "Y/N"}),
    Type = Table.TransformColumnTypes(ROC,{{"first scan", type datetime}})
in
    Type[/SIZE]
Click to expand...

Sweet ! thanks, i had the same idea about the last part.
 
Upvote 0
here is all together:

Code: 
[SIZE=1]// Query1
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source1, "Index", 1, 1),
    Source2 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source2, {"Shipping number"}, {{"Max", each List.Max([first scan]), type datetime}, {"Min", each List.Min([first scan]), type datetime}, {"Count", each _, type table}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Max] - [Min], type duration),
    Condition = Table.AddColumn(Subtract, "Y/N", each if [Subtraction] > #duration(0, 1, 0, 0) then "Yes" else "No"),
    ROC = Table.SelectColumns(Condition,{"Shipping number", "Y/N"}),
    Result = Table.NestedJoin(ROC,{"Shipping number"},Index,{"Shipping number"},"Result",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Result, "Result", {"piece number", "first scan", "Index"}, {"piece number", "first scan", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Shipping number", "piece number", "first scan", "Index", "Y/N"}),
    Type = Table.TransformColumnTypes(Reorder,{{"first scan", type datetime}}),
    ROC1 = Table.SelectColumns(Type,{"Shipping number", "piece number", "first scan", "Y/N"})

in
    ROC1[/SIZE]
 
Upvote 0
Cross-posted: https://www.excelforum.com/excel-fo...-dates-time-when-other-value-is-matching.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
301
awoohaw
awoohaw
L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
118
Peter_SSs
Peter_SSs
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
217
freeb1893
F
D
  • Question
Cannot make formula SPILL down
Replies
3
Views
382
Peter_SSs
Peter_SSs
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
227
Lukma
L

Forum statistics

Threads
1,214,627
Messages
6,120,610
Members
448,973
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top