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 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.