Forum Discussion
Summarizing inconsistent text format in column
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 , try using below steps
Load your data into Power Query.
Select the column with the delimited time values.
Go to the "Transform" tab and select "Split Column" -> "By Delimiter".
Choose the delimiter (e.g., comma) and split into rows.
Add a custom column to parse the time components. Use the following formula to extract each component and convert it to minutes:
let
Source = [Status 1],
TimeComponents = Text.Split(Source, " "),
Minutes = List.Sum(List.Transform(TimeComponents, each
if Text.EndsWith(_, "M") then Number.FromText(Text.Start(_, Text.Length(_) - 1)) * 43200 else
if Text.EndsWith(_, "w") then Number.FromText(Text.Start(_, Text.Length(_) - 1)) * 10080 else
if Text.EndsWith(_, "d") then Number.FromText(Text.Start(_, Text.Length(_) - 1)) * 1440 else
if Text.EndsWith(_, "h") then Number.FromText(Text.Start(_, Text.Length(_) - 1)) * 60 else
if Text.EndsWith(_, "m") then Number.FromText(Text.Start(_, Text.Length(_) - 1)) else 0
))
in
Minutes
Go to the "Transform" tab, select "Group By".
Group by "Key" and add a new column that sums the minutes.
Add a custom column to convert the total minutes back to the desired format:
let
TotalMinutes = [TotalMinutes],
Months = Number.IntegerDivide(TotalMinutes, 43200),
RemainingMinutes1 = Number.Mod(TotalMinutes, 43200),
Weeks = Number.IntegerDivide(RemainingMinutes1, 10080),
RemainingMinutes2 = Number.Mod(RemainingMinutes1, 10080),
Days = Number.IntegerDivide(RemainingMinutes2, 1440),
RemainingMinutes3 = Number.Mod(RemainingMinutes2, 1440),
Hours = Number.IntegerDivide(RemainingMinutes3, 60),
Minutes = Number.Mod(RemainingMinutes3, 60),
Result = Text.Combine(
List.Select(
{
if Months > 0 then Text.From(Months) & "M" else null,
if Weeks > 0 then Text.From(Weeks) & "w" else null,
if Days > 0 then Text.From(Days) & "d" else null,
if Hours > 0 then Text.From(Hours) & "h" else null,
if Minutes > 0 then Text.From(Minutes) & "m" else null
},
each _ <> null
),
" "
)
in
Result