Power Query, Convert Decimal to Hours and Minutes

[uk747]

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

name	Decimal		name	Decimal
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​

 

[Alex Blakenburg]

Try selecting Duration instead of Time. Then in Excel format the column as [h]:mm:ss

 

[uk747]

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

 

[Alex Blakenburg]

No worries. Glad it's working for you now.

 

[AlienSx]

@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.

 

[uk747]

@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

 

[AlienSx]

    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]}
    )

 

[uk747]

Thanks alien