cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bamse
Frequent Visitor

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 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).

1 ACCEPTED SOLUTION
Bamse
Frequent Visitor

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)

View solution in original post

3 REPLIES 3
Bamse
Frequent Visitor

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)

View solution in original post

dax
Community Support
Community Support

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
Frequent Visitor

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
}

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors