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

[Pot2000]

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 to split the data as below

Days	hours	min
30	22	3
0	1	7
0	0	57

Any help is welkom

[Ahmedx · Accepted solution]

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"

[Pot2000]

Works like a charm thank you

[Pot2000]

Some extra info i use the following formulas in excel. And than copy past values and  make numbers from text.
=Iferror(Left(E3;search("d";E3)-1);0)
=iferror(iferror(mid(E3;search("u";E3)-2;2);mid(E3;search("u";E3)-1;1));0)

=iferror(mid(E3;search("m";E3)-2;2);0)

I than load this file into POwerBI.

Howeever i would like to do this in Powerbi since it is someting that comes back every quater with 3 differant dumps.