Converting hh:mm:ss to hh:mm

[masplin]

I have inherited a modle with a huge number of times hh:mm:ss and think it  the precision is uneccessary and causing a lot of additional strain. Is there an easy way to reformat these as hh:mm in Power Q?

Thanks

Mike 

[amitchandak · Accepted solution]

In the current column, it will only change display

create a new column

new column = format(table[time],"HH:MM") and change data time

or

new column = time(hour(table[time]),minute(table[time]),0)

[masplin]

Great presumably this just tuncated so 14:30:44 becomes 14:30? Is there an equivalent to rounding so it became 14:31?

Thanks

Mike

[v-gizhi-msft]

Hi,

Please try this calculated column:

Column = IF(SECOND('Test'[Time])<=30,FORMAT('Test'[Time],"HH:MM"),FORMAT(TIME(HOUR('Test'[Time]),MINUTE('Test'[Time])+1,0),"HH:MM"))

And it shows:

Best Regards,

Giotto Zhi

[masplin]

Do you have an M equivalent as trying to reduce the cardinality in the model so needs to be done in the query?

Thanks

[d_gosbell]

---

@masplin wrote:

Do you have an M equivalent as trying to reduce the cardinality in the model so needs to be done in the query?

---

To convert the DAX formula with the IF to a similar M expression you could do the following

= if Time.Second([Custom]) > 30 
then #time(Time.Hour([Custom]), Time.Minute([Custom])+1, 0)
else #time(Time.Hour([Custom]), Time.Minute([Custom]),0)

In theory I figured that the first expression would be much more expensive due to casting to and from a string. But I did some rough benchmarking and it is slower than the #time constructor, but only by about 5% in my tests. 

[d_gosbell]

You could add a new custom column with the following formula

Time.FromText( Time.ToText([Column1],"HH:mm"))

Then delete the original column and rename this new column 

[d_gosbell]

Actually I think the following expression is probably better than my previous suggestion converting to and from a string:

#time(Time.Hour([Column1]), Time.Minute([Column1]),0) 

 (I got this suggestion from Imke at https://thebiaccountant.com )

[masplin]

why is this better as your first suggestion seeme dfine?

Thanks

Mike