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"

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

Hi Anonymous thanks for sharing your nice challenge

you can copy the below code and paste it into the advance editor
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 1" = _t]),
L = {{"M","*30*24*60"},{"w","*7*24*60"},{"d","*24*60"},{"h","*60"},{"m","*1"},{",","+"},{" ","+"}},
Convert = Table.AddColumn(Source, "new", each List.Accumulate(L,_[Status 1], (a,b)=> Text.Replace(a,b{0},b{1}))),
#"Added Custom" = Table.AddColumn(Convert, "Total In Minute", each Expression.Evaluate([new])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [
a=[Total In Minute],
x=Number.IntegerDivide,
y=Text.From,
M=x(a,30*24*60),
w=x(a-M*30*24*60,7*24*60),
d=x(a-M*30*24*60-w*7*24*60,24*60),
h=x(a-M*30*24*60-w*7*24*60-d*24*60,60),
m=a-M*30*24*60-w*7*24*60-d*24*60-h*60,
r=y(M)&"M "&y(w)&"w "&y(d)&"d "&y(h)&"h "& y(m)&"m "
][r])
in
#"Added Custom1"