cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
QQQ
Advocate I
Advocate I

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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

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
Community Champion
Community Champion

Change the duration to text.

Specializing in Power Query Formula Language (M)

It does not change anything.

 

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)

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors