How to get Data type text, to Data type time to calculate total duration per event

[WmUit]

Hello Community,

I am making calculations about the duration of multiple events, that's has been calculated with help from an earlier discussion, but the given data is in data type ''text'',  which makes it impossible to calculate the total duration per event (I think it needs to be data type ''time'' to make any calculations). The table is shown below.

I made calculations to get the duration when an events starts (gekomen) and when an events ends (gegaan), that duration is per event_ID. I want to know what the total duration per event_ID is. (the ''Calculated Column Duration'' in data type: Text, which makes it hard to make any calculations)

My question is, is there an methode to transform this ''Text'' data to''Time'' data (calculated column duration-->Duration in Time column(Data type: Time in minutes)

Greetings,

Wmuit

[Anonymous]

@WmUit 

Use Power Query to do this. I think there is a datatype in M that is called duration and such things like 60:12 can be easily converted to duration. You can add and subtract durations and they will behave as you'd like them to.

[amitchandak]

@WmUit , Are you able to change the data type in column properties to datetime or time ? If not what is the error ?

how to handle Duration, refer
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[WmUit]

Hello @amitchandak, I can't convert values above 24:00, (the error is: cannot convert value 52:32:00 of type text to type Date.) Is it possible to filter everything above 24:00h, because there are 7 values above 24h and those values are to high to take in my calculations, so its fine if these values are filtered out and then the data type can be change to time.

Many thanks for you effort!

Greetings Wmuit