Forum Discussion

Bamse's avatar
Bamse
Frequent Visitor
6 years ago
Solved

Need to convert date-time and durations to ticks in PowerQuery

I am fetching data from a data source which requires me splitup my queries and provide a start and stop time for each fetched block. It only accepts times as clock ticks. Question: Is there a cl...
Need Help
  • Bamse's avatar
    Bamse
    6 years ago

    I've resolved the issue by adding a column with the number of ticks for the start of each day to my calendar table.
    This gives me at least the tick count, though I've not (yet) investigated how this is affected by leap seconds and leap years in the future.
    But for the time range 2000-01-01 until 'NOW' it appears to produce correct results and gets the job done.

    For those interested in the code:
    BljnTicks20000101 = Number.FromText("630822816"),
    BljnTicksPerDay = Number.FromText("864"),
    #"AddBljnTicks" = Table.AddColumn(#"Define PK", "BljnTicks", each (BljnTicks20000101 + (Duration.Days([Date] - Date.FromText("2000-01-01"))) * BljnTicksPerDay)

Bamse's avatar
Bamse
Frequent Visitor
6 years ago

I've resolved the issue by adding a column with the number of ticks for the start of each day to my calendar table.
This gives me at least the tick count, though I've not (yet) investigated how this is affected by leap seconds and leap years in the future.
But for the time range 2000-01-01 until 'NOW' it appears to produce correct results and gets the job done.

For those interested in the code:
BljnTicks20000101 = Number.FromText("630822816"),
BljnTicksPerDay = Number.FromText("864"),
#"AddBljnTicks" = Table.AddColumn(#"Define PK", "BljnTicks", each (BljnTicks20000101 + (Duration.Days([Date] - Date.FromText("2000-01-01"))) * BljnTicksPerDay)