Search code examples
powerbidaxpowerquerydaxstudio

Filter only to show durtion


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


Solution

  • 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"
    

    enter image description here