Forum Discussion

PowerBI88's avatar
PowerBI88
Icon for Resolver I rankResolver I
5 years ago
Solved

Split data field from Service Now

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

Need Help
Show and Tell
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    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"

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago
    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"