Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Data (time format) Transformation

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.

Need Help

1 Reply

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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!