none
Iterate over timestamps RRS feed

  • Question

  • As part of a large data set, I have a column with timestamps (=X1) and product temperatures (=X2).

    For example, the first part of the X1 column looks like:

    8-3-2017  7:23:08

    8-3-2017  7:23:18

    8-3-2017  7:24:49

    8-3-2017  7:29:52

    For example, the first part of the X2 column looks like:

    80,7

    80,3

    79,2

    77,2

    I would like to add with Power Query a third column (=X3), which gives the process time in seconds. This means a kind of iteration over the timestamps

    The output of the Power Query should look like:

    0

    10

    101

    401

    How could this be realized with Power Query?

    Thanks in advance!

    Wednesday, March 22, 2017 2:45 PM

Answers

  • From your examples I understand you want to subtract the first timestamp from the other timestamps?

    It can be done with the following code, in which your example data is imported from an Excel file.
    The last step was crated by choosing Duration - Total Seconds on the Transform tab.

    The last value is 404, not 401.

    let
        Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Technet Power Query\Iterate over timestamps.xlsx"), null, true),
        Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"X1", type datetime}, {"X2", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "X3", each [X1]-#"Changed Type"[X1]{0}),
        #"Calculated Total Seconds" = Table.TransformColumns(#"Added Custom",{{"X3", Duration.TotalSeconds, type number}})
    in
        #"Calculated Total Seconds"

    • Marked as answer by Frank data Thursday, March 23, 2017 10:38 AM
    Wednesday, March 22, 2017 6:28 PM

All replies

  • From your examples I understand you want to subtract the first timestamp from the other timestamps?

    It can be done with the following code, in which your example data is imported from an Excel file.
    The last step was crated by choosing Duration - Total Seconds on the Transform tab.

    The last value is 404, not 401.

    let
        Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Technet Power Query\Iterate over timestamps.xlsx"), null, true),
        Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"X1", type datetime}, {"X2", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "X3", each [X1]-#"Changed Type"[X1]{0}),
        #"Calculated Total Seconds" = Table.TransformColumns(#"Added Custom",{{"X3", Duration.TotalSeconds, type number}})
    in
        #"Calculated Total Seconds"

    • Marked as answer by Frank data Thursday, March 23, 2017 10:38 AM
    Wednesday, March 22, 2017 6:28 PM
  • Dear Marcel,

    I was able to solve the problem.

    Thank you very much for your support!

    Best regards,

    Frank

    Thursday, March 23, 2017 10:39 AM