Data (time format) Transformation

[daugela-gytis]

Hello I am having problems transforming time duration. The problem is I am getting the data from XML type file. Time format in the file looks :

Uptime	Downtime
176d 08h 07m 06s	01h 05m 00s
01h 05m 00s	03d 03h 40m 00s
00s	27m 31s
27m 31s	00s

PowerBi engine recognizes the data as Text if i try to change to anything elses it gives me an error. 
The biggest problem is that not every row has the same amount of data meaning I can't split it by special character or position because then the data divides separately. If I delete the letters powerBI engine still can't detect data as time or duration.
 
I guess it would be possible to format using m - query language in advanced editor but I am not there yet. 

Any help would be appreciated.

[ValtteriN]

Hi,

You could try to clean the data using find an replace. E.g. find "h" and replace it with "". This way you will get data that you can transform without issues or split based on delimiter " "

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!