Extract number values from one column to multiple columns

[Anonymous]

Hello!

Can anybody help me to transform the columns values (Date Difference) which are not constant into multiple columns as shown below: Its an urgent request from my client who need exact 4 columns as output for all the time frames. Thanks in advance.

Input	output
Date difference	Week(w)	Day(d)	hours(h)	minutes(m)
1w 5d 18h 54m	1	5	18	54
1d 1h 3m	-	1	1	3
2h 20m	-	-	2	20
10m	-	-	-	10

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMixXME1RMLTIUDA1yVWK1QGKALkZCsYQjlGGgpEBVBxKG5UbZygoKBgZQbiWKUCOoQWQEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date difference" = _t]),

    Split = Table.AddColumn(Source, "Split",
        each let
                txt = Text.Remove([Date difference], " "),
                chars = Text.Select(txt, {"a".."z"})
            in
                Record.FromList(List.RemoveLastN(Text.SplitAny(txt, chars)), Text.ToList(chars))
    ),
    #"Expanded Split" = Table.ExpandRecordColumn(Split, "Split", {"w", "d", "h", "m"})
in
    #"Expanded Split"

[Anonymous]

Hi @CNENFRNL ,

Thanks for the quick reply,

As I am new to power BI, Can you please let me the steps to execute your code (or) where to copy your code.

Can i copy your code to custom column ? if so then I am getting values as table, table... in the new column. 

PLease guide me. thanks

[ryan_mayu]

@Anonymous 

maybe you can transform the data in PQ. 

pls see the attachment below