Converting string ex. "117:50:00" into hours

[adrianoramos520]

Well. I have two collums with a start service and other with the date closed as pic below:

I inserted a new collumn with the code:

(h):mm:ss =

var Hora= QUOTIENT('Incidentes (2)'[Segundos],3600)

var Minut0= QUOTIENT('Incidentes (2)'[Segundos]-'Incidentes (2)'[var Hora]*3600,60)

var Segundo='Incidentes (2)'[Segundos]-'Incidentes (2)'[var Hora]*3600-'Incidentes (2)'[var Minuto]*60

return

CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE('Incidentes (2)'[var Hora],":"),'Incidentes (2)'[var Minuto]),":"),'Incidentes (2)'[var Segundos])

And I got the result I wanted but as "String" and I'm not able to convert it into hours. So I can't find the time average and others calculations I expected.

When I try to convert it I get the message: "Its not possible convert the value '117:50:0' type of String into Date".

What am I doing wrong? Could you please help me?

Thanks a lot. 

[v-eachen-msft · Accepted solution]

Hi @adrianoramos520 ,

You could use "Split" feature to get total hours in query editor.

Here is my result and test file for your reference.

[adrianoramos520]

Thanks for everyone that tried to help me. @v-eachen-msft its worked fine for me, and now I can perform some measures.

Thank you so much.

[mahoneypat]

I see you are trying to solve this with a DAX column but why not do it in the query editor.  Do you need the hh:mm:ss format?  Or is that a step to get you toward total hours?  If the latter, you can get the total hours between the two columns with Duration.TotalHours([Resolved] - [Created]).  This will give total hours as a decimal number, which you can round to the nearest hour, if needed.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

Have you tried this ?

([Resolvido] - [Criado em]) * 24

[adrianoramos520]

Yeah but I got a negative number.... and I still can't convert it into hours. Take a look.

[lbendlin]

Can you show the power query code and some data samples in a table?

[lbendlin]

Given the range of your negative numbers I guess that your "Resolved" column only has time but no date.

[adrianoramos520]

I think its not the case. 

Criado em	Resolvido
01/05/2020 00:37	01/05/2020 01:57
01/05/2020 00:56	05/05/2020 14:51
01/05/2020 01:25	01/05/2020 03:47
01/05/2020 01:30	01/05/2020 01:55
01/05/2020 01:48	01/05/2020 12:22
01/05/2020 01:54	04/05/2020 17:05
01/05/2020 02:04	01/05/2020 02:10
01/05/2020 03:03	01/05/2020 09:03
01/05/2020 03:17	26/05/2020 10:11
01/05/2020 06:32	07/05/2020 11:52
01/05/2020 06:54	01/05/2020 06:57
01/05/2020 10:01	01/05/2020 10:24
01/05/2020 10:11	04/05/2020 17:00
01/05/2020 10:15	01/05/2020 14:51

[lbendlin]

i think i know what it is. Your columns are "Variant" data type, and are being interpreted as numbers. Change the columns to type "DateTime"

[adrianoramos520]

Its a good point but I converted as date/hour but not worked. After conversion I performed a subtraction and the result was the same.

Take a look. Ex. 4.21:50:00  Its a "string" 4days21hours:50min  The idea is to have it in hh:mm:ss  Ex. 117:50:00 and I have it, but When I try to convert it as hour DAX says that cannot convert string 117:50:00 as hour.

[lbendlin]

Change the subtraction column to number and multiply it by 24