Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Summarizing inconsistent text format in column

I have a data source that provides "time spent" in various statuses, however it comes with delimited for all times it changes status, meaning one row can look like this "1M 0w 1d 20h 0m,1w 1d 20h 14...
Need Help
  • ThxAlot's avatar
    ThxAlot
    1 year ago

    Simple enough,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVI5EsQgDPuKJzUFPjjyiH3BznYp0qTO91dgk3S2JYFk+H43zpy3tMlNehDzScbX9ksD0AKAbyoHiZ4kdiXzktviWAfHPmQ3ZWACrC+sy6vPOHi/kjynlXoldUXJV6q4fD9Jc4jZ6hB/CHp2mpaFVR6OP5RvksDqwpo8OnsMJZDFyegfbh/n6HTOHQYlAMltAJE1hmrqccBGmoJtSDCWMa3yULh5MJjnipJR3hQxTR04aS1Le3EvHIyYmw4rEgbX2qvNqbz6unPw2rxsTfWdWnCLxmqzrxZh8jQXDdvq4E8Fjz4DaeTxdGj2mcFNhCB7DVk4rXOTcVobF81x9z93EELJ/Acj3nz83x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Status = _t]),
    Replacement = {{"M", "*30*1440"}, {"w", "*7*1440"}, {"d", "*1440"}, {"h", "*60"}, {"m", ""}, {" ", "+"}, {",", "+"}},
    #"Transformed minutes" = Table.TransformColumns(Source, {"Status", each Expression.Evaluate(List.Accumulate(Replacement, _, (s,c) => Text.Replace(s, c{0}, c{1})))})
in
    #"Transformed minutes"

ThxAlot's avatar
ThxAlot
Super User
1 year ago

Simple enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVI5EsQgDPuKJzUFPjjyiH3BznYp0qTO91dgk3S2JYFk+H43zpy3tMlNehDzScbX9ksD0AKAbyoHiZ4kdiXzktviWAfHPmQ3ZWACrC+sy6vPOHi/kjynlXoldUXJV6q4fD9Jc4jZ6hB/CHp2mpaFVR6OP5RvksDqwpo8OnsMJZDFyegfbh/n6HTOHQYlAMltAJE1hmrqccBGmoJtSDCWMa3yULh5MJjnipJR3hQxTR04aS1Le3EvHIyYmw4rEgbX2qvNqbz6unPw2rxsTfWdWnCLxmqzrxZh8jQXDdvq4E8Fjz4DaeTxdGj2mcFNhCB7DVk4rXOTcVobF81x9z93EELJ/Acj3nz83x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Status = _t]),
    Replacement = {{"M", "*30*1440"}, {"w", "*7*1440"}, {"d", "*1440"}, {"h", "*60"}, {"m", ""}, {" ", "+"}, {",", "+"}},
    #"Transformed minutes" = Table.TransformColumns(Source, {"Status", each Expression.Evaluate(List.Accumulate(Replacement, _, (s,c) => Text.Replace(s, c{0}, c{1})))})
in
    #"Transformed minutes"

Anonymous's avatar
Anonymous
Not applicable
1 year ago

ThxAlot 

 

Thanks for the assist! This worked great, I had to add something to handle nulls but the rest was great. Thanks!