Calculate time between dates on 2 different rows

[Patrick95]

Hi All,

I'm looking for a way to calculate the time between 2 dates on 2 different rows:

The data is from picking orders in a warehouse. Each picking process has an unique Document No. and is continued via Status codes. The purpose is to measure the average picking time per day/userID/document No.  

In my example below I want to calculate the time between the Starting date and the End date that are located in differend rows.

 

Hope you guys can help!

Greetings,

Patrick

[SebSchoon1 · Accepted solution]

I ve created this table with kind of same structure.

Then Group By ID

Then add a column with the "add personalized column"

Paste this inside

= Table.AddColumn(#"Lignes groupées", "Personnalisé", each Table.FillDown([Nombre],{"End date"}))

explanation

Delete column [Nombre]

expand [Personnalisé]

Then add logic 🙂

Then append tables.

You can also try to keep file name by type of selected source (ex: folder)

[v-yalanwu-msft]

Hi, @Patrick95 ;

Is your problem solved?  If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Patrick95 ;

You could try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLStdA1NFAwNLYytLAyMAAKKsXqQGTR2DDFhgqGhlZGliDFIFknJClTBUNTK2NTJHOcUPUCFZhZGYAtio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DocumentNo = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DocumentNo", type text}, {"Start", type datetime}, {"End", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DocumentNo"}, {{"Count", (x)=> Table.AddColumn(x, "Picking Time", each Duration.ToRecord(List.Max(x[End]) - List.Min(x[Start])), Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Start", "End", "Picking Time"}, {"Start", "End", "Picking Time"}),
    #"Expanded Picking Time" = Table.ExpandRecordColumn(#"Expanded Count", "Picking Time", {"Days", "Hours", "Minutes", "Seconds"}, {"Days", "Hours", "Minutes", "Seconds"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Picking Time",{{"Hours", type text}, {"Minutes", type text}, {"Seconds", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","0","00",Replacer.ReplaceValue,{"Hours", "Minutes", "Seconds"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value", "duration", each Text.Combine({Text.From([Days], "zh-CN"), Text.From([Hours], "zh-CN"), Text.From([Minutes], "zh-CN"), Text.From([Seconds], "zh-CN")}, ":"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Hours", "Minutes", "Seconds"})
in
    #"Removed Columns"

The final show:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Patrick95]

With some adjustments on my part I succeeded! thanks!

[SebSchoon1]

Hi, 

Maybe you could Group the data by Order ID , 

Then in the column which contains all the created tables, you fill down the end date column

Then create conditional column, 

if start date is not null and end date is not null Then do "End date" minus "Start date"  else = null

then expand ?

🙂

[SebSchoon1]

Has that worked for you? if Yes could you click on the solution button?

it would be my first one ^^

[Patrick95]

Hi, 

No unfortunatley not, I have replied on your post earlier. 

Gre, 

Patrick

[Patrick95]

Hi Seb,

Unfortunately this didn't help, but thanks for your answer.

Greetings,

Patrick

[SebSchoon1]

Hello,

For your Grouping do like this.

Group By ID (only) then by table  

You'll see all the id's in left column and column containing tables on the right column

In these tables, you can write formulas to drill down (or up) specific columns

then follow the logic i told you, then expand, it should work

maybe tomorrow i'll have the time to show with an example

[Patrick95]

Ok I follow you, I've got the ID's in the left column now and the containing tables in the right.

How do I "write formulas to drill down (or up) specific columns"? 

Would be great if you can show me 🙂 It's driving me nuts xD

[SebSchoon1]

Hello,

has it worked? as desired output?

[SebSchoon1 · Accepted solution]

I ve created this table with kind of same structure.

Then Group By ID

Then add a column with the "add personalized column"

Paste this inside

= Table.AddColumn(#"Lignes groupées", "Personnalisé", each Table.FillDown([Nombre],{"End date"}))

explanation

Delete column [Nombre]

expand [Personnalisé]

Then add logic 🙂

Then append tables.

You can also try to keep file name by type of selected source (ex: folder)

[SebSchoon1]

To calculate Days and time between End date and Start date there are multiple possibilities

From the menu. Select first [End date] then [Start date]

Then go here 

OR add personnalized column

and type

Then expand record

to get (after replacing (null) errors

[SebSchoon1]

good !! i'm on it !

i'm trying to show you something with which you should only change according to your columns names

[Patrick95]

Hi Seb, everything OK? 

Now the next thing I have to do is calculate the hours between the two dates and EXCLUDE the weekends (Sat and Sunday). Do you have any idea how to achieve this? Like to hear from you again. Greetings.

[jbwtp]

Hi @Patrick95,

I think this conceptually what you are after. Could you pleae have a look?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MgIylGJ1ICJobGNDfXOQithYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNo = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNo", type text}, {"Start", type date}, {"End", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"OrderNo"}, {{"Count", (x)=> Table.AddColumn(x, "Picking Time", each Duration.TotalDays(List.Max(x[End]) - List.Min(x[Start])), Int64.Type)}}),
    #"Expanded Count" = Table.Combine(#"Grouped Rows"[Count])
in
    #"Expanded Count"

Thanks,

John

[Patrick95]

Hi John,

Thanks for your quick reply!

Seems like it will give the same result (211) unfortunately.

Could you please take another look? 

Thanks!

Patrick

[jbwtp]

Hi @Patrick95,

Do you mind sharing your query code?

Unless you copied everything including the Source step from my code, I can't see a reson for getting the 211 in the Picking Time column. If you can provide a code, I will write exactly how it needs to look like, so you could copy/paste it and move forward.

Kind regards,

John