Power Query, Convert Decimal to Hours and Minutes

uk747

Well-known Member
Joined
Jul 20, 2011
Messages
843
Office Version
  1. 365
Platform
  1. Windows
Currently have data below and want to convert to hours and minutes
I loaded to PQ and just changed type for Decimal Column to Time and it works e.g. 0.4 = 9:36, however if its greater than 24 hours e.g. 1.5 I want it to show 36:00:00

nameDecimalnameDecimal
a
0.4​
a
09:36:00​
b
0.6​
b
14:24:00​
c
1.5​
c
d
0.2​
d
04:48:00​
e
0.45​
e
10:48:00​
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try selecting Duration instead of Time. Then in Excel format the column as [h]:mm:ss
 
Upvote 0
Solution
Thanks. I did try duration = Table.TransformColumnTypes(Source,{{"name", type text}, {"Decimal", type duration}})

but 1.5 showed as 1 12:00:00 so was trying to edit in formula bar e.g. add , "hh:mm:ss" and "[h]:mm:ss"

guess trying to overcomplicate
 
Upvote 0
@uk747, PQ <> Excel. PQ is ETL tool. It's for data transformation, not for data presentation. PQ is dealing with data types, not formats. If you need to transform decimal number into something that looks like you described above then you need to apply a transformation of decimal number to text. That can be done easily and type number >> type duration (or period of time in other words) transformation is just a half way to do that. Let us know if you really need to transform your decimal into text in PQ.
 
Upvote 0
@uk747, PQ <> Excel. PQ is ETL tool. It's for data transformation, not for data presentation. PQ is dealing with data types, not formats. If you need to transform decimal number into something that looks like you described above then you need to apply a transformation of decimal number to text. That can be done easily and type number >> type duration (or period of time in other words) transformation is just a half way to do that. Let us know if you really need to transform your decimal into text in PQ.

Hi
If there's a way to do that as well I could use as an extra option thanks
 
Upvote 0
Power Query:
    tx = Table.TransformColumns(
        Source, 
        {"decimal", (x) => 
            [dur = Duration.ToRecord(Duration.From(x)),
            txt = Number.ToText(dur[Days] * 24 + dur[Hours], "00") 
                & ":" & 
                Number.ToText(dur[Minutes], "00") 
                & ":" & 
                Number.ToText(dur[Seconds], "00.#")][txt]}
    )
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top