Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Extract number values from one column to multiple columns

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.

 

Inputoutput   
Date differenceWeek(w)Day(d)hours(h)minutes(m)
1w 5d 18h 54m151854
1d 1h 3m-113
2h 20m--220
10m---10
Need Help

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

     

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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