none
Duration in days query RRS feed

  • Question

  • Hello all,

    I have a time/date stamp and require to calculate duration in days from today's date.

    Can anyone assist with a power query to achieve this?

    Thank you, Ben

    Wednesday, April 22, 2015 4:47 AM

Answers

  • Please follow these steps in the Query Editor:

    1. Select the time/date column (by clicking the column header).
    2. Click Transform -->Date-->Age
    3. Click Transform-->Duration-->Day

    These two transform operations will change the selected column to the duration.

    Here is the query expression that was generated when I follow these steps on a data imported using Power Query-->From Table

    The data resides in Table1. The time/date column is "Column1". You should note that these operations will calculate the duration by your local time.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Age = Table.TransformColumns(Source,{{"Column1", each DateTime.LocalNow() - _, type duration}}),
        Days = Table.TransformColumns(Age,{},Duration.Days)
    in
        Days

    • Proposed as answer by Gil RavivMVP Wednesday, April 22, 2015 3:46 PM
    • Marked as answer by numerouno10 Wednesday, April 29, 2015 7:53 AM
    Wednesday, April 22, 2015 3:45 PM