Forum Discussion
Solved
Split Values in Power BI Query
Hello, How to split below values in Power BI Query Editor? Sample Values: col 1 1h 40m 56s 23m 45s 34s Expected Results: col 1, col 2, col 3 1, 40, 56 blank, 23, 45 blank, bla...
Like this:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMk5RMDTJUDAyzVUwNCxWitWJVjLMUDAxyFUwNYNwjYxzFUxMIWxjEyAdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_TotalSeconds = Table.AddColumn(Source, "TotalSeconds", each [ a = {{"d", "*86400"}, {"h", "*3600"}, {"m", "*60"}, {"s", "*1"}}, b = List.Accumulate( a, {}, (s,c)=> s & { if not Text.Contains([Column1], c{0}) then null else Text.Replace(List.Select(Text.Split([Column1], " "), (x)=> Text.Contains(x, c{0})){0}?, c{0}, c{1}) } ), c = List.Transform(b, (x)=> try Expression.Evaluate(x) otherwise null), d = List.Sum(c) ][d], Int64.Type), Ad_Splitted = Table.AddColumn(Ad_TotalSeconds, "Splitted", each [ a = Text.SplitAny([Column1], "dhms"), b = List.Transform(List.Select(a, (x)=> Text.Trim(x) <> ""), Number.From), c = List.Repeat({null}, 4 - List.Count(b)) & b, d = Table.FromList({c}, (x)=> x, type table[Days=Int16.Type, Hours=Int64.Type, Minutes=Int64.Type, Seconds=Int64.Type]) ][d], type table), ExpandedSplitted = Table.ExpandTableColumn(Ad_Splitted, "Splitted", {"Days", "Hours", "Minutes", "Seconds"}) in ExpandedSplitted
Hi MadhavDholakia, another solution. You can decide whether you need TotalSeconds or separately Hours, Minutes and Seconds.
If you don't know how to use my query - read note below my post.
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMsxQMDHIVTA1K1aK1YlWMjLOVTAxhbCNTYB0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_TotalSeconds = Table.AddColumn(Source, "TotalSeconds", each
[ a = {{"h", "*3600"}, {"m", "*60"}, {"s", "*1"}},
b = List.Accumulate( a, {}, (s,c)=> s & { if not Text.Contains([Column1], c{0}) then null else Text.Replace(List.Select(Text.Split([Column1], " "), (x)=> Text.Contains(x, c{0})){0}?, c{0}, c{1}) } ),
c = List.Transform(b, (x)=> try Expression.Evaluate(x) otherwise null),
d = List.Sum(c)
][d], Int64.Type),
Ad_Splitted = Table.AddColumn(Ad_TotalSeconds, "Splitted", each
[ a = Text.SplitAny([Column1], "hms"),
b = List.Transform(List.Select(a, (x)=> Text.Trim(x) <> ""), Number.From),
c = List.Repeat({null}, 3 - List.Count(b)) & b,
d = Table.FromList({c}, (x)=> x, type table[Hours=Int64.Type, Minutes=Int64.Type, Seconds=Int64.Type])
][d], type table),
ExpandedSplitted = Table.ExpandTableColumn(Ad_Splitted, "Splitted", {"Hours", "Minutes", "Seconds"}, {"Hours", "Minutes", "Seconds"})
in
ExpandedSplitted
thanks dufoq3 - this has worked as expected. There is only one scenario, apologies that was not involved in my initial question, which is not being handled here.
so in case if the value is as given below, i.e., including Day Value - it shows error. Can you please suggest how I can modify your solution to add this as well?
Thank you.
| 2d 7h 34m 58s |
| 1d 5h 4m 10s |
Like this:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMk5RMDTJUDAyzVUwNCxWitWJVjLMUDAxyFUwNYNwjYxzFUxMIWxjEyAdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_TotalSeconds = Table.AddColumn(Source, "TotalSeconds", each [ a = {{"d", "*86400"}, {"h", "*3600"}, {"m", "*60"}, {"s", "*1"}}, b = List.Accumulate( a, {}, (s,c)=> s & { if not Text.Contains([Column1], c{0}) then null else Text.Replace(List.Select(Text.Split([Column1], " "), (x)=> Text.Contains(x, c{0})){0}?, c{0}, c{1}) } ), c = List.Transform(b, (x)=> try Expression.Evaluate(x) otherwise null), d = List.Sum(c) ][d], Int64.Type), Ad_Splitted = Table.AddColumn(Ad_TotalSeconds, "Splitted", each [ a = Text.SplitAny([Column1], "dhms"), b = List.Transform(List.Select(a, (x)=> Text.Trim(x) <> ""), Number.From), c = List.Repeat({null}, 4 - List.Count(b)) & b, d = Table.FromList({c}, (x)=> x, type table[Days=Int16.Type, Hours=Int64.Type, Minutes=Int64.Type, Seconds=Int64.Type]) ][d], type table), ExpandedSplitted = Table.ExpandTableColumn(Ad_Splitted, "Splitted", {"Days", "Hours", "Minutes", "Seconds"}) in ExpandedSplitted