Identifying consecutive rows with criteria

[PBOBOP]

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.

Test dates have been sorted in descending order according to item number. (I have given the item numbers different colours to help to differentiate them): 

Item number	Test Date	Results
248694	22/01/2024	Pass	This row is not considered because the results for test in the next row is "Fail"
248694	15/01/2024	Fail
248694	8/01/2024	Fail
248694	18/12/2023	Fail
248694	11/12/2023	Fail
248694	4/12/2023	Pass
248694	27/11/2023	Pass
248694	20/11/2023	Fail
248692	23/01/2024	Fail
248692	17/01/2024	Pass
248692	9/01/2024	Pass
248692	19/12/2023	Pass
248692	12/12/2023	Fail
248690	10/08/2023	Fail
248690	3/08/2023	Fail
248690	27/07/2023	Pass
248690	21/07/2023	Pass
248690	12/07/2023	Fail
248690	7/07/2023	Fail
248690	30/06/2023	Fail
248686	26/03/2024	Fail
248686	21/03/2024	Fail
248686	18/03/2024	Pass
248686	14/03/2024	Pass
248686	11/03/2024	Fail
248686	6/03/2024	Fail
248684	2/11/2023	Pass	This row is not considered because the results for test in the next row is "Fail"
248684	30/10/2023	Fail
248684	25/10/2023	Pass	This row is not considered because the results for test in the next row is "Fail"
248684	18/10/2023	Fail
248684	11/10/2023	Pass
248684	4/10/2023	Pass
248684	27/09/2023	Pass
248684	20/09/2023	Pass

My desired outcome: 

Item number	Latest Pass Date	Second Latest Pass Date
248694	4/12/2023	27/11/2023
248692	9/01/2024	19/12/2024
248690	27/07/2023	21/07/2023
248686	18/03/2024	11/03/2024
248684	11/10/2023	4/10/2023

Is there any measure that can do this?

Thank you

[lbendlin · Accepted solution]

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 

[v-xuxinyi-msft]

Hi @PBOBOP 

Your solution is great, @Ashish_Mathur and @lbendlin. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

Here is my testing.

1. First add indexed columns grouped together in Power Query.

Table.Group(Source, {"Item number"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}})

2. Expand the table

I also changed the column names.

Here's the complete code in advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdM9CoQwEAXgu6QWkpnE/Fxg6+3FwnJhO+8PRuMsuLyZTvKR8ObFLIvjVHNLbnLkmT0HPr/f2767dXoozaKv7fP912oge7o4qkwmJ1EQq+8sNgd8No+RI849lAouZGgzsM/UcCxhxrFCX6melNBDo45FreNWstQKVXzRMfuIE9fcV3reDGsWJUupwp5Fk6nmyUoo+XFgU0ODOu/NPJu7lffwY3xLwvg9XNq0+xcNT10P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item number" = _t, #"Test Date" = _t, Results = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Item number"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}}),
    #"Expanded index" = Table.ExpandTableColumn(#"Grouped Rows", "index", {"Test Date", "Results", "Index.1"}, {"index.Test Date", "index.Results", "index.Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded index",{{"index.Test Date", "Test Date"}, {"index.Results", "Results"}, {"index.Index.1", "Index.1"}})
in
    #"Renamed Columns"

Close & Apply

3. Create two measures as follow

rankDate = MAX([Test Date])

rank = IF(MAX([PassResult]) = 1, RANKX(ALLEXCEPT('Table', 'Table'[Item number]), [rankDate], , ASC, Dense), BLANK())

4. Create a calculated column as follows

PassResult = 
VAR _pIndex = [Index.1] - 1
VAR _nIndex = [Index.1] + 1
VAR _lr = CALCULATE(MAX([Results]), FILTER(ALLEXCEPT('Table', 'Table'[Item number]), [Index.1] = _pIndex))
VAR _rl = CALCULATE(MAX([Results]), FILTER(ALLEXCEPT('Table', 'Table'[Item number]), [Index.1] = _nIndex))
VAR _cl = [Results]
VAR _result = IF( _lr = "Pass" && _cl = "Pass" || _cl = "Pass"&& _rl = "Pass", 1, 0)
RETURN
_result

5. Create two measures as follow

Latest Pass Date = 
VAR _maxRank = MAXX(ALLEXCEPT('Table', 'Table'[Item number]), [rank])
VAR _maxDate = CALCULATE(MAX([Test Date]), FILTER('Table', [rank] = _maxRank))
RETURN
_maxDate

Second Latest Pass Date = 
VAR _maxRank = MAXX(ALLEXCEPT('Table', 'Table'[Item number]), [rank])
VAR _maxDate = CALCULATE(MAX([Test Date]), FILTER('Table', [rank] = _maxRank - 1))
RETURN
_maxDate

Result:

I have noticed some discrepancies between your expected results and those labeled in the data sheet. Please feel free to ask me if I have misunderstood.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[lbendlin · Accepted solution]

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