Change a text type to a duration time

[wtmartinoms]

I am trying to change the following table (currently have over 8,000 rows) to a  Data Type: Duration. Curently is in Data Type: Text. Need to sum all the durations in a Power BI Dashboard, but when editing or changing the Data Type on Power Query it gives me an Error Output. 

Time

4 hours 10 minutes

1 hour 43 minutes

53 minutes

1 day 20 minutes

3 days 7 hours 33 minutes

8 hours

7 hours 1 minute

Need Help 

THANKS

[Mariusz · Accepted solution]

Hi @wtmartinoms 

Try this script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHIyC8tKlYwNFDIzcwrLUktVorViVYyBAsrmBijiJoaoylKSaxUMELVaQwSLFYwh5prjKrFAiIMZsOUGEJVKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let 
            lst = Text.Split( [Time], " " ),
            Value = List.Transform( List.Alternate( lst, 1, 1, 1 ), Number.FromText ),
            Name = List.Transform( List.Alternate( lst, 1, 1 ), each Text.Start( _, 1 ) ),
            rec = Record.FromList( Value, Name )
        in 
            #duration( 
                try rec[d] otherwise 0, 
                try rec[h] otherwise 0,
                try rec[m] otherwise 0, 0 
            ), type duration 
        )
in
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[wtmartinoms]

 @Mariusz 

This would help. Currently is bringing me the user table i used as example. 

Trying to change it to the colunm I need to change (who holds over 8,000 rows)