Time | Value |
---|---|
10/3/2022 18:21:40 | correct |
10/3/2022 18:22:50 | incorrect |
10/3/2022 18:28:00 | correct |
10/3/2022 18:34:00 | incorrect |
From the above table, I want only filter out and show on the table if the time difference between "correct" and "incorrect" is > 5 minutes
This assumes there is always only two alternating rows of correct and incorrect, and returns a column showing the duration in minutes between them. You can then filter that, since you were vague on how and what to filter
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type datetime}, {"Value", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "MinuteDuration", each if [Value] = "incorrect" then null else Duration.TotalMinutes(#"Added Index"{[Index]+1}[Time]-[Time]),type number),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"MinuteDuration"})
in #"Filled Down"