Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a Excel report that has the Duration entered with the following format below. When I pull the data into my power query to add up the time, Power Query changes the data to decimal point. I just want the duration to appear in Power Query. The only way I can see doing this is to create a table in excel not using Power Query to create a lookup. Any ideas how I can do this using formula?
Duration | Duration | Would like | |||
0:25 | 0.017361 | 25 | |||
0:25 | 0.017361 | 25 | |||
1:00 | 0.041667 | 60 | |||
0:45 | 0.03125 | 45 | |||
0:20 | 0.013889 | 20 | |||
0:10 | 0.006944 | 10 | |||
0:30 | 0.020833 | 30 | |||
1:00 | 0.041667 | 60 | |||
8:00 | 0.333333 | 480 | |||
1:00 | 0.041667 | 60 | |||
0:30 | 0.020833 | 30 | |||
0:15 | 0.010417 | 15 | |||
1:00 | 0.041667 | 30 | |||
0:30 | 0.020833 | 30 | |||
1:30 | 0.0625 | 90 | |||
1:00 | 0.041667 | 60 | |||
1:00 | 0.041667 | 60 | |||
0:15 | 0.010417 | 15 | |||
0:40 | 0.027778 | 40 |
Solved! Go to Solution.
@STIBBS_807
If you need to get the seconds from decimal then add the following custom column:
Number.Round ( [Duration] * 1440 , 0 )
If you need to convert it to duration then you can change the type to decimal and add the following column
then change type to Duration
Duration.From([your decimal])
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@STIBBS_807
If you need to get the seconds from decimal then add the following custom column:
Number.Round ( [Duration] * 1440 , 0 )
If you need to convert it to duration then you can change the type to decimal and add the following column
then change type to Duration
Duration.From([your decimal])
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello! Here you go. If the Duration column should is of type duration (changing the type to duration should should make the column appear as in the screensnip below with hour, min and sec) then you can use Duration.TotalSeconds([Duration]) function.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwAiIjUyUdJSARq4NLxBAoCBQxM0BSYwJSY4KiC6TGCFmNIUjEEFnEGCRibIDHZAuIiIkFPkWYBgEtAznIEMPRxvitB4tY4rMLm+3odgGDA+xooJpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t, #"Would like" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type duration}, {"Would like", Int64.Type}}),
#"Inserted Total Seconds" = Table.AddColumn(#"Changed Type", "Total Seconds", each Duration.TotalSeconds([Duration]), type number)
in
#"Inserted Total Seconds"
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |