Calculate time difference based on id and sequence number in different tables

[danielmbaquero]

Good day community,

I have two tables. The first one is called "An_Start" and it shows me the time a particular operation (OperationID) start the analysis. In other table called "Reg_Start" I have the time an operation starts the regularization process. Any given operation can start analysis many times. Also, in both tables, I have a column indicating the sequence of starts, so I know if it was the first, second, third, etc. time that the particular operation started analysis or regularization process.

I want to calculate the difference in time between the first start of regularization process and first start of analysis, the same for the second, third, and go on.

The "An_start" table looks like this:

ID	Analysis Start Time	Sequence
1	15/2/2019 12:30	1
1	15/2/2019 15:30	2
1	16/2/2019 7:30	3
1	16/2/2019 11:30	4
2	11/2/2019 10:00	1
2	12/2/2019 8:00	2
3	10/2/2019 18:00	1
4	22/2/2019 7:30	1
4	23/2/2019 11:00	2

The "Reg_Start" table looks like this:

ID	Regularization Start Time	Sequence
1	15/2/2019 13:30	1
1	15/2/2019 19:30	2
1	16/2/2019 10:30	3
2	11/2/2019 17:00	1
2	12/2/2019 11:00	2
3	11/2/2019 7:00	1
4	22/2/2019 9:30	1

I want to have a new table that looks like this:

ID	Sequence	Analysis time
1	1	1:00:00
1	2	4:00:00
1	3	3:00:00
2	1	7:00:00
2	2	3:00:00
3	1	13:00:00
4	1	2:00:00

[nandukrishnavs]

@danielmbaquero 

Try this in Edit Query

An_start

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyzEvMqSzOLFYILkksKlEIycxNBYoGpxaWpuYlpyrF6kQrGQIFDE31jfSNDAwtFQyNrIwNQCLY5EwhckYIOTOYnDlEyhiLlKEhRM4ELGcEkjOEyxlYGSCsA8sZweQsIFIQ24xBUgZwbRbI2kxAqozQXIIkZYzkEpiRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Analysis Start Time", type datetime}, {"Sequence", Int64.Type}})
in
    #"Changed Type1"

Reg_Start

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKSk0vzUksyqxKLMnMz1MILkksKlEIycxNBcoFpxaWpuYlpyrF6kQrGQIFDE31jfSNDAwtFQyNrYwNQCLY5CwhckYIOTO4nAFEzhgsZwSSM4TLmVsZIMwEyxnB5QwhchAzjVH0oWgzASmCa7OEuzIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Regularization Start Time", type datetime}, {"Sequence", Int64.Type}})
in
    #"Changed Type1"

Merge1

let
    Source = Table.NestedJoin(An_start, {"ID", "Sequence"}, Reg_Start, {"ID", "Sequence"}, "Reg_Start", JoinKind.LeftOuter),
    #"Expanded Reg_Start" = Table.ExpandTableColumn(Source, "Reg_Start", {"Regularization Start Time"}, {"Reg_Start.Regularization Start Time"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Reg_Start", "Analysis time", each [Reg_Start.Regularization Start Time]-[Analysis Start Time]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Analysis time] <> null))
in
    #"Filtered Rows"

 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[jthomson]

If you make a new column in each table concatenating the id and sequence numbers (with some sort of separator i.e. your top line is something like 1-1) then you should be able to merge or relate the tables, it's then just a case of some sort of datediff formula