Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 14m,1w 1d 1h 32m,4w 0d 3h 50m,1w 0d 1h 59m,1d 22h 8m,1w 1d 0h 8m,1h 37m"
I'm wondering if there's a way to create a custom power query column that somehow summarizes this into just one section?
Another tricky part is that of course not all rows have the same amount of delimitors, nor the same set of time components.
One row could have just days, or just hours. while some may have a combination of above etc.
Example:
Key | Status 1 |
1010 | 2w 3d 11h 41m |
1035 | 1w 5d 23h 24m,4d 23h 17m |
1048 | 4M 4w 0d 22h 18m |
1082 | 1w 5d 20h 49m,2w 5d 23h 56m,3d 22h 50m,6d 19h 30m |
1146 | 1M 1w 1d 22h 35m |
1161 | 2M 0w 2d 22h 36m |
1172 | 1M 1w 4d 22h 18m,2M 2w 2d 2h 16m |
1181 | 3w 0d 18h 42m |
1207 | 3d 23h 1m |
1343 | 1w 0d 0h 54m,2d 23h 15m |
1362 | 1w 0d 17h 50m,1d 16h 51m,1w 6d 19h 43m,1d 1h 8m |
1385 | 3w 1d 19h 4m |
1437 | 2d 18h 47m |
1647 | 22h 8m |
1691 | 2d 17h 51m |
1693 | 2d 17h 48m |
536 | 1M 0w 1d 20h 0m,1w 1d 20h 14m,1w 1d 1h 32m,4w 0d 3h 50m,1w 0d 1h 59m,1d 22h 8m,1w 1d 0h 8m,1h 37m |
607 | 1w 1d 17h 0m |
810 | 2d 8h 2m,3d 18h 30m |
Solved! Go to Solution.
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hello again!
I did encounter an issue for some rows that also had a Year (Y) parameter in their string
I altered the "Replacement" string in advanced editor to the following: (I want sum of hours)
Replacement = {{"Y","*365*24"},{"M", "*30*24"}, {"w", "*7*24"}, {"d", "*24"}, {"h", ""}, {"m", "/60"}, {" ", "+"}, {",", "+"}},
But I still get an error with those rows containing year end up as null:
Example:
1y 0M 1w 0d 19h 35m
1y 0M 0w 0d 18h 26m
{"y","*365*24"}
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Thanks for the assist! This worked great, I had to add something to handle nulls but the rest was great. Thanks!
Hi @thne123 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"
You could use a Python script for it:
import pandas as pd
import numpy as np
def convert_to_minutes(row):
# Original string
time_string = row['Status 1']
# Predefined order of suffixes and their conversion factors to minutes
time_units = {
"Y": 365 * 24 * 60, # Years to minutes
"M": 30 * 24 * 60, # Months to minutes (approximate, assuming 30 days per month)
"w": 7 * 24 * 60, # Weeks to minutes
"d": 24 * 60, # Days to minutes
"h": 60, # Hours to minutes
"m": 1 # Minutes
}
# Create list for all the converted entries
converted_times = []
# Split by commas to handle multiple time components (e.g., "2w, 3d, 11h, 41m")
for times in time_string.split(","):
time_in_minutes = 0 # Initialize total time for this entry
# Parse the string and calculate the total minutes for each part
for part in times.split():
suffix = part[-1] # Get the suffix (last character)
# Check if the suffix is valid and exists in the time_units dictionary
if suffix in time_units:
value = int(part[:-1]) # Extract the numeric value
time_in_minutes += value * time_units[suffix] # Convert and accumulate the total
# Append the calculated time for this part to the list
converted_times.append(time_in_minutes)
# Sum the total times for all parts in the row
total_time = np.sum(np.asarray(converted_times))
return total_time
# Apply the function to create a new column for total time
dataset['total time in minuts'] = dataset.apply(convert_to_minutes, axis=1).astype(int)
Resultat in:
Did I solve your question? Mark my post as a solution! Kudos are appreciated as well as LinkedIn endorsements.
Good to see someone incorporating py script.
The script is also simple enough, cheers!
import re
repl = {
"Y": '* 365 * 24 * 60', # Years to minutes
"M": '* 30 * 24 * 60', # Months to minutes (approximate, assuming 30 days per month)
"w": '* 7 * 24 * 60', # Weeks to minutes
"d": '* 24 * 60', # Days to minutes
"h": '* 60', # Hours to minutes
"m": '', # Minutes
",": '+',
" ": '+'
}
ptn = re.compile('[a-zA-Z, ]')
dataset['Minutes'] = dataset.apply(lambda r: eval(ptn.sub(lambda m: repl[m.group()], r['Status'])), axis=1)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
@thne123 , 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
Proud to be a Super User! |
|
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |