Forum Discussion

PBOBOP's avatar
PBOBOP
Helper I
2 years ago
Solved

Identifying consecutive rows with criteria

Hi everyone,    I would like to retrieve the latest 2 dates when an item passes a test twice in a row, i.e. there should not be any failed tests between the 2 passes. Please see my sample data. Te...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago

    No need for measures.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdO9DoUgDAXgd2E2oS3Izwvc+e7GwdHkbr5/cikOYoAzmi8th1q2zYhPIXuzGBFLbIVEP77HdZl9aZjXhj/H+XtzgsrJsii7MTNk32gfTKJlBkwNv3uLspsGV+Y4HYpyhsp5GryyTG9NymQpzdlBLUOhOD66MkMusR7um0eoruQOQ05Bjw6W3HDgNzPkskgPv4Lf7DHj5vNkdZFma1a13Lr8sOGta/HacF+t7wNU6/sA1R6qrkIGTB3vfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item number" = _t, #"Test Date" = _t, Results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item number", Int64.Type}, {"Test Date", type date}, {"Results", type text}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item number", "Results"}, {{"Rows", each _, type table},{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Results] = "Pass" and [Count] > 1),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Latest Pass", each [Rows]{0}[Test Date ],type date),
    Custom1 = Table.AddColumn(#"Added Custom", "Second Latest Pass", each [Rows]{1}[Test Date ],type date),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Item number", "Latest Pass", "Second Latest Pass"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

     

    Obligatory credit: ImkeF