Hi @GotManyProblems ,
Try the updated code below. It gives me the following ouput (see new Invoicing activity):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMjJV0lHyzCvLz0zOzEtXitUBippaGZgBRQMSk7MT05FEjQ2Aok5FqYnZMBETS6BISH5mTmoJRMjMysAQU6uZlYkRUDQ4Nz87FSJibmVojqbV3MrICFOrhZUByNbgjMy0EgXXvBSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Activity = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Timestamp", type time}, {"Activity", type text}}),
sortChronological = Table.Sort(chgTypes,{{"Timestamp", Order.Ascending}}),
addIndex1 = Table.AddIndexColumn(sortChronological, "Index1", 1, 1, Int64.Type),
addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
mergeSelfOnIndex = Table.NestedJoin(addIndex0, {"Index1"}, addIndex0, {"Index0"}, "Added Index0", JoinKind.LeftOuter),
fixMergeSorting = Table.Sort(mergeSelfOnIndex,{{"Index1", Order.Ascending}}),
expandSelf = Table.ExpandTableColumn(fixMergeSorting, "Added Index0", {"Timestamp"}, {"endTime"}),
filterShiftEnd = Table.SelectRows(expandSelf, each ([endTime] <> null)),
remIndexes = Table.RemoveColumns(filterShiftEnd,{"Index1", "Index0"}),
addListOfHours = Table.AddColumn(remIndexes, "hourSplit", each List.Transform({Time.Hour([Timestamp])..Time.Hour([endTime])}, each Time.From(Text.Combine({Text.From(_),"00"},":")))),
expandListOfHours = Table.ExpandListColumn(addListOfHours, "hourSplit"),
addActivityDurMins = Table.AddColumn(expandListOfHours, "activityDurMins", each if Time.Hour([hourSplit]) <> Time.Hour([Timestamp]) and Time.Hour([hourSplit]) <> Time.Hour([endTime]) then 60
else if [Timestamp] >= [hourSplit] and Time.Hour([Timestamp]) = Time.Hour([endTime]) then Duration.Minutes([endTime] -[Timestamp])
else if [Timestamp] >= [hourSplit] and Time.Hour([Timestamp]) < Time.Hour([endTime]) then Duration.Minutes([hourSplit] - [Timestamp]) + 60
else if [Timestamp] <= [hourSplit] then Duration.Minutes([endTime] -[hourSplit])
else null),
filterZeroMins = Table.SelectRows(addActivityDurMins, each ([activityDurMins] <> 0)),
addTimeRange = Table.AddColumn(filterZeroMins, "timeRange", each let
timeFrom = Text.Combine({Text.From(Time.Hour([hourSplit])), "00"}, ":"),
timeTo = Text.Combine({Text.From(Time.Hour([hourSplit]) + 1), "00"}, ":")
in
Text.Combine({timeFrom, timeTo}, "-")),
remOthCols = Table.SelectColumns(addTimeRange,{"timeRange", "Activity", "activityDurMins"}),
pivotActivities = Table.Pivot(remOthCols, List.Distinct(remOthCols[Activity]), "Activity", "activityDurMins", List.Sum)
in
pivotActivities
Pete
Hi @GotManyProblems ,
It's a bit of a beast, but the code below turns this:
Into this:
Create a new blank query in PQ then, in Advanced Editor, paste this over the default code. You will then be able to follow the steps I took:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjBQ0lEKSEzOTkzPzEtXitUBixqDRJ2KUhOzYSImlkCRkPzMnNQSiJCZlYEhplYzKxMjoGhwbn52KkTE3MrQHE2ruZWREaZWC4hbgjMy00oUXPNSlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Activity = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Timestamp", type time}, {"Activity", type text}}),
sortChronological = Table.Sort(chgTypes,{{"Timestamp", Order.Ascending}}),
addIndex1 = Table.AddIndexColumn(sortChronological, "Index1", 1, 1, Int64.Type),
addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
mergeSelfOnIndex = Table.NestedJoin(addIndex0, {"Index1"}, addIndex0, {"Index0"}, "Added Index0", JoinKind.LeftOuter),
fixMergeSorting = Table.Sort(mergeSelfOnIndex,{{"Index1", Order.Ascending}}),
expandSelf = Table.ExpandTableColumn(fixMergeSorting, "Added Index0", {"Timestamp"}, {"endTime"}),
filterShiftEnd = Table.SelectRows(expandSelf, each ([endTime] <> null)),
remIndexes = Table.RemoveColumns(filterShiftEnd,{"Index1", "Index0"}),
addListOfHours = Table.AddColumn(remIndexes, "hourSplit", each List.Transform({Time.Hour([Timestamp])..Time.Hour([endTime])}, each Time.From(Text.Combine({Text.From(_),"00"},":")))),
expandListOfHours = Table.ExpandListColumn(addListOfHours, "hourSplit"),
addActivityDurMins = Table.AddColumn(expandListOfHours, "activityDurMins", each if [Timestamp] >= [hourSplit] and Time.Hour([Timestamp]) = Time.Hour([endTime]) then Duration.Minutes([endTime] -[Timestamp])
else if [Timestamp] >= [hourSplit] and Time.Hour([Timestamp]) < Time.Hour([endTime]) then Duration.Minutes([hourSplit] - [Timestamp]) + 60
else if [Timestamp] <= [hourSplit] then Duration.Minutes([endTime] -[hourSplit])
else null),
filterZeroMins = Table.SelectRows(addActivityDurMins, each ([activityDurMins] <> 0)),
addTimeRange = Table.AddColumn(filterZeroMins, "timeRange", each let
timeFrom = Text.Combine({Text.From(Time.Hour([hourSplit])), "00"}, ":"),
timeTo = Text.Combine({Text.From(Time.Hour([hourSplit]) + 1), "00"}, ":")
in
Text.Combine({timeFrom, timeTo}, "-")),
remOthCols = Table.SelectColumns(addTimeRange,{"timeRange", "Activity", "activityDurMins"}),
pivotActivities = Table.Pivot(remOthCols, List.Distinct(remOthCols[Activity]), "Activity", "activityDurMins", List.Sum)
in
pivotActivities
Summary:
1) Sort the data chronologically on [Timestamp] and add offset indexes.
2) Merge table on itself to get activity start and end time inline.
3) Create list of hours covered by activity to duplicate rows for activities that span before end of hour/after start of hour.
4) Calculate individual activity times splitting by time hour.
5) Calculate the time range in which the activity occurred.
6) Pivot activities with activity duration as aggregate (sum) field.
Pete
Helpful resources
Power BI Monthly Update - September 2025
Check out the September 2025 Power BI update to learn about new features.
