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 hav...
Need Help
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 🙂