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

[Bamse]

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 clever and reliable way to convert a date-time stamp and/or a duration to ticks in PowerQuery (M)?

With reliable I mean both:
Which takes into account leap seconds e.c.t..
Which will work with a scheduled refresh (does not require a local component).

[Bamse · Accepted solution]

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)

[dax]

Hi @Bamse , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bamse]

Hi Zoe,

There's no .PBIX yet because I am in the 'feasability' stage where I am testing if I can get access to all data I need.
I've build a fetch tester in PowerShell, and this fetches two test intervals from the web site as a test.
This works, and now I'd like to traslate this to Power Query (M) such that I can fetch the data with something like below.
The part in the PowerShell script that does the trick is (Get-Date $timestamp).Ticks but I do not find anything alike for PowerQuery (M).

StartTicks = (SOME CALCULATION),
EndTicks = (SOME CALCULATION),
Json.Document(Web.Contents(""https://WebSite.com?output=json",
[Query = [
start=StartTicks,
end=EndTicks],
Headers = [Authorization = Bearer]])),

Below the code in PowerShell I used to test that I can successfully fetch the page.

# QnD JSON FETCH TESTER
# Requires >= PS6.0

$Bearer = "1234567890abcdefg"
$Dates = "2020-04-10 00:00", "2020-04-11 00:00"

Foreach ($Date in $Dates) {
$start = (Get-Date $Date).Ticks
$end = (Get-Date $Date).AddDays(1).Ticks -1
$uri = "https://WebSite.com?output=json&start=$start&end=$end"
$OutFile = "C:\temp\fetch_day_$((Get-Date $Date).Day).JSON"

$response = Invoke-WebRequest -UseBasicParsing -Uri $uri -Authentication OAuth -Token (ConvertTo-SecureString $Bearer -AsPlainText -Force) #-UseBasicParsing ).Content
$response.Content | Out-File $OutFile
}