A Power Query / BI newbie here. I have the following sample data table:
| DateTime | Process | Activity | ElapsedProcessTime(s) | TimeInterval(s) (expected output) |
|---|---|---|---|---|
| 17/Jul/2024 00:00:00 | A | 1 | 0 | 0 |
| 17/Jul/2024 00:10:00 | A | 2 | 600 | 600 |
| 17/Jul/2024 00:15:01 | A | 3 | 901 | 301 |
| 17/Jul/2024 00:15:45 | A | 4 | 945 | 44 |
| 17/Jul/2024 01:00:00 | B | 1 | 0 | 0 |
| 17/Jul/2024 01:10:00 | B | 2 | 600 | 600 |
| 17/Jul/2024 01:15:01 | B | 3 | 901 | 301 |
| 17/Jul/2024 01:15:45 | B | 4 | 945 | 44 |
| 17/Jul/2024 14:00:00 | C | 1 | 0 | 0 |
| 17/Jul/2024 14:05:00 | C | 2 | 300 | 300 |
| 17/Jul/2024 14:15:01 | C | 3 | 901 | 601 |
| 17/Jul/2024 14:15:55 | C | 4 | 955 | 54 |
I need a Power Query formula that calculates the TimeInterval(s) column using the data from the other columns as shown above. The Time Interval value needs to be difference between the current and previous Elapsed Time value, resetting to zero whenever Process has changed.
I can do this in Excel easily by referencing the previous cell and calculating the difference using a simple formula and conditional statements, but I just can’t figure out how to do this in Power Query.
Try this below.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index2", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "GroupOnMe", each try if #"Added Index"{[Index2]-1}[Process] = [Process] then null else [Index2] otherwise [Index2]),
#"Filled Down" = Table.FillDown(#"Added Custom",{"GroupOnMe"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"GroupOnMe"}, {{"data", each
let a=Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)
in Table.AddColumn(a, "TimeInterval(s)", each try [#"ElapsedProcessTime(s)"]-a{[Index]-1}[#"ElapsedProcessTime(s)"] otherwise [#"ElapsedProcessTime(s)"])
, type table }}),
#"Expanded data" = Table.ExpandTableColumn( Table.RemoveColumns(#"Grouped Rows",{"GroupOnMe"}), "data", {"DateTime", "Process","Activity", "ElapsedProcessTime(s)", "TimeInterval(s)"}, {"DateTime", "Process","Activity", "ElapsedProcessTime(s)", "TimeInterval(s)"})
in #"Expanded data"
1
I think you can replicate your Excel formula in power query by sorting your data by Process and DateTime first, and then add a “custom column” in power query that mirrors your Excel formula.
To reference “previous” row, you just need to add an index column (like in previous answer).
The M code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
sorted = Table.Sort(Source, {{"Process", Order.Ascending}, {"DateTime", Order.Ascending}}),
addIndex = Table.AddIndexColumn(sorted, "Index", 0, 1, Int64.Type),
calcTimeInterval = Table.AddColumn(addIndex, "TimeInterval(s)", each if [Index] = 0 or[Process] <> sorted[Process]{[Index] - 1} then 0 else [#"ElapsedProcessTime(s)"] - sorted[#"ElapsedProcessTime(s)"]{[Index] - 1}),
removeUnused = Table.RemoveColumns(calcTimeInterval, {"Index"})
in
removeUnused