Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ElliotP
Post Prodigy
Post Prodigy

Adding Duration to a Start time to achieve a time value

Morning,

 

I've got a duration value for a product for example of 2500seconds; how would I add this value in a column/measure to say an established start time like 7am so that it comes out as 7.41am?

2 ACCEPTED SOLUTIONS

This video shows how it's done with my example. It starts in the query editor with the initial data (after step 1 in my query).

Specializing in Power Query Formula Language (M)

View solution in original post

Well, the picture explains a lot.

 

First of all, your numbers of seconds are much more than something like a few hours (or a part of it, as in your original example), so it doesn't make sense to add it to a time without date, unless you acually want to start at December 30, 1899, 7:00 AM.

(Date numbering in the Power.... environment is based on the numbering in Excel starting with 1 on 1/1/1900, but unfortunately the year 1900 was considered a leap year.... so day 60 in Excel is the non existing 2/29/1900; day 61 is 3/1/1900 both in Excel and the Power ... environments, so calculating back to 0 brings you to December 30, 1899, 12:00:00 AM (midnight)).

 

If you are adding a number to a datetime as a new column, then you are adding days.

So if you want to add seconds, you need to divide the number by the number of seconds in a day, being 86400 (24 * 60 * 60).

Your example of 1,555,200 seconds brings you to 1/17/1900 7:00:00 AM.

 

Watch this video, to which I added a short demo of Camtasia Studio, the software I'm using to create videos.

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

14 REPLIES 14
MarcelBeug
Community Champion
Community Champion

Your example translated to Power Query:

 

let
    Source = #table(type table[Start = time, Duration in seconds = number],{{#time(7, 0, 0), 2500}}),
    #"Added Custom1" = Table.AddColumn(Source, "End", each [Start] + #duration(0,0,0,[Duration in seconds]), type time)
in
    #"Added Custom1"
Specializing in Power Query Formula Language (M)

Thanks for the quick response. I'm not very strong with powerquery and I already have a few tables which have used power query to get to my final stage. I've attached my pbix;

 

'Stocks Sold' is using powerquery to work out the amount of stock sold per day.

 

'Stock Turnover' is the table where I bring it all together. I would ideally like to add the value in "Stock (in seconds)" to the start time of 7am.

 

Pbix Link: https://1drv.ms/u/s!At8Q-ZbRnAj8hk25Rwb3njrRokHF

Your link requires a Microsoft Azure Marketplace subscription which I don't have, so I get errors with all queries.

 

 

Specializing in Power Query Formula Language (M)

hmm

 

Might be easier to do it as a custom column; any idea how to do that? Something like:

 

 = #time(7,0,0) + [DurationInSeconds]

?

That's what I did in my first answer.

Specializing in Power Query Formula Language (M)

I'm not sure how to take the power query command and put that into a custom column.

 

Assuming I create the opening hour time and call it [OpeningHourTime] and set it to 7am in the Time format. How would I add the duration in seconds to that in a custom column?

This video shows how it's done with my example. It starts in the query editor with the initial data (after step 1 in my query).

Specializing in Power Query Formula Language (M)

That's amazing. Thank you so much.

 

How did you make the video, it's very tidy?

https://gyazo.com/be272a4f5d3077e8d44533e1af0833f3

 

I have [Stock  (in seconds)] as a calculated column (not created from the query). I've been able to create the opening time in correct format, but i'm unable to add the [Stock (in seconds)] to it successfully. [Stock (in seconds)] is in the whole number format and I'm not sure how to change it to duration.

 

I'm unable to see [Stock (in seconds)] in the query editor, am I still able to use it as a custom column? Or is there a way to change something to a duration value in the report view?

 

A function like TIME or TIMEVALUE for duration perhaps?

 

Well, the picture explains a lot.

 

First of all, your numbers of seconds are much more than something like a few hours (or a part of it, as in your original example), so it doesn't make sense to add it to a time without date, unless you acually want to start at December 30, 1899, 7:00 AM.

(Date numbering in the Power.... environment is based on the numbering in Excel starting with 1 on 1/1/1900, but unfortunately the year 1900 was considered a leap year.... so day 60 in Excel is the non existing 2/29/1900; day 61 is 3/1/1900 both in Excel and the Power ... environments, so calculating back to 0 brings you to December 30, 1899, 12:00:00 AM (midnight)).

 

If you are adding a number to a datetime as a new column, then you are adding days.

So if you want to add seconds, you need to divide the number by the number of seconds in a day, being 86400 (24 * 60 * 60).

Your example of 1,555,200 seconds brings you to 1/17/1900 7:00:00 AM.

 

Watch this video, to which I added a short demo of Camtasia Studio, the software I'm using to create videos.

 

 

Specializing in Power Query Formula Language (M)

And it will break down to showing the correct time from that?

Add a date to your Start.

Specializing in Power Query Formula Language (M)

After reading over and over it again and again (I just wasn't getting it); I finally understood that I was using the seconds column, instead of the days column which was causing me to stand still when it came to my plus column.

 

Thank you so much, I really appreciate, it really does mean a lot to me.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.