Forum Discussion

danielmbaquero's avatar
danielmbaquero
Regular Visitor
6 years ago

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

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:

IDAnalysis Start TimeSequence
115/2/2019 12:301
115/2/2019 15:302
116/2/2019 7:303
116/2/2019 11:304
211/2/2019 10:001
212/2/2019 8:002
310/2/2019 18:001
422/2/2019 7:301
423/2/2019 11:002

 

The "Reg_Start" table looks like this:

IDRegularization Start TimeSequence
115/2/2019 13:301
115/2/2019 19:302
116/2/2019 10:303
211/2/2019 17:001
212/2/2019 11:002
311/2/2019 7:001
422/2/2019 9:301

 

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

 

IDSequenceAnalysis time
111:00:00
124:00:00
133:00:00
217:00:00
223:00:00
3113:00:00
412:00:00
Need Help

2 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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     🙂