Forum Discussion

Pot2000's avatar
Pot2000
Regular Visitor
2 years ago
Solved

Split colum when value is missing the null value should be in the first colum

I have a data dump where one of the colums contains a period of time that i would like to split in day's hours and minutes. The data is as follows. 30d 22h 3m 1h 17m 57m I would like t...
  • Ahmedx's avatar
    2 years ago

    pls try code

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZIUTAyylAwzlWK1YlWMsxQMDSHME1BdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [
    a = Text.Split([Data]," "),
    b= List.Transform(a,(x)=> Splitter.SplitTextByCharacterTransition({"0".."9"},(x)=>not List.Contains({"0".."9"},x))(x)),
     c= Table.FromList(b,(x)=>x),
     e= Table.TransformColumns(c,{"Column2",(x)=> if x =
     "d" then "Days" else if x= "h" then "hours" else "min"})  ][e]),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Column2]), "Column2", "Column1")
    in
        #"Pivoted Column"