Split data field from Service Now

[PowerBI88]

Hi,

from service now I get a date in the column task:latable_busienss_duration in the following format: "10 days 3 hous 10 minutes", I would like to split this column so that I will have a column with only days, a column with only hours and a column with only minutes.

here is a screenshot of the data: 

Can you provide me some hint?

Thank you

Kind regards

Marta

[PowerBI88]

thank you , it worked 🙂

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSIT0msLI43js/ILy0qjgfyczPzSktSi5VidaKVTCCypqYoooYg0XhDsBawAFw3VJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Elapsed = _t]),
    #"Removed Plural" = Table.AddColumn(Source, "a", each Text.Remove([Elapsed], "s")),
    Mapping = Table.TransformColumns(#"Removed Plural", {"a", each let l = List.Zip(List.Split(Text.Split(_, "_"),2)) in Record.FromList(l{0}, l{1})}),
    #"Expanded Elapsed" = Table.ExpandRecordColumn(Mapping, "a", {"day", "hour", "minute"}, {"day", "hour", "minute"})
in
    #"Expanded Elapsed"