cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
QQQ Regular Visitor
Regular Visitor

Purpose of "Duration" datatype in Power Query

I am editing data in Power Query 

 

Parsed "Occurred On" into Date and Time. Can you tell me how I would use "Duration". 

 

dateandtime.PNG

 

time.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Purpose of "Duration" datatype in Power Query

Hi @QQQ,

 

I think you can take a look at bleow links which about duration.

 

Reference:

Function Description
Duration.Days Returns the day component of a Duration value.
Duration.From Returns a duration value from a value.
Duration.FromText Returns a Duration value from a text value.
Duration.Hours Returns an hour component of a Duration value.
Duration.Minutes Returns a minute component of a Duration value.
Duration.Seconds Returns a second component of a Duration value.
Duration.ToRecord Returns a record with parts of a Duration value.
Duration.TotalDays Returns the total magnitude of days from a Duration value.
Duration.TotalHours Returns the total magnitude of hours from a Duration value.
Duration.TotalMinutes Returns the total magnitude of minutes from a Duration value.
Duration.TotalSeconds Returns the total magnitude of seconds from a duration value.

 

For instance:

Duration.Second([Datetime Column]) //get the second value(0~59) of datetime

Duration.TotalSeconds([Datetime Column]) //convert datetime value to second

 

Sample: get the diff of date range and convert them to second.

2.PNG3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Purpose of "Duration" datatype in Power Query

Hi @QQQ,

 

I think you can take a look at bleow links which about duration.

 

Reference:

Function Description
Duration.Days Returns the day component of a Duration value.
Duration.From Returns a duration value from a value.
Duration.FromText Returns a Duration value from a text value.
Duration.Hours Returns an hour component of a Duration value.
Duration.Minutes Returns a minute component of a Duration value.
Duration.Seconds Returns a second component of a Duration value.
Duration.ToRecord Returns a record with parts of a Duration value.
Duration.TotalDays Returns the total magnitude of days from a Duration value.
Duration.TotalHours Returns the total magnitude of hours from a Duration value.
Duration.TotalMinutes Returns the total magnitude of minutes from a Duration value.
Duration.TotalSeconds Returns the total magnitude of seconds from a duration value.

 

For instance:

Duration.Second([Datetime Column]) //get the second value(0~59) of datetime

Duration.TotalSeconds([Datetime Column]) //convert datetime value to second

 

Sample: get the diff of date range and convert them to second.

2.PNG3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Purpose of "Duration" datatype in Power Query

Another example in this topic in which "epoch" (seconds since 1/1/1970 0:00) is converted to UTC datetime and local date.

(In this example, epoch is a number in text format, that's why I used Number.From).

 

 

Specializing in Power Query Formula Language (M)
AnetaK Member
Member

Re: Purpose of "Duration" datatype in Power Query

Hello,

 

How can I change the "Custom" column from your example to a duration format like YY:MM:DD.HH:MM:SS?

 

My End-Start column is simply [End]-[Start].

Duration... is Duration.TotalSeconds([End]-[Start]).

 

I would like to get a column that looks like End-Start in the Query Editor, so sth like 1.15:20:30.

 

If I change the format of Duration... to "Time" I get an error "We can't automatically convert the column to Time type."

 

What should I do now?

 

Przechwytywanie2.PNGPrzechwytywanie.PNG

MarcelBeug Super Contributor
Super Contributor

Re: Purpose of "Duration" datatype in Power Query

Change the duration to text.

Specializing in Power Query Formula Language (M)
AnetaK Member
Member

Re: Purpose of "Duration" datatype in Power Query

It does not change anything.

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors