Forum Discussion
Calculate time between dates on 2 different rows
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
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)
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.
17 Replies
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.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 ?
🙂
Hi Seb,
Unfortunately this didn't help, but thanks for your answer.
Greetings,
Patrick
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
Has that worked for you? if Yes could you click on the solution button?
it would be my first one ^^
Hi,
No unfortunatley not, I have replied on your post earlier.
Gre,
Patrick
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
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
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
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.With some adjustments on my part I succeeded! thanks!
