Conditional New Column To Filter Data

[TMoose]

I'm new to Power BI so struggling with a way to filter my data.  Below is a sample of the data where I want to create a new column to capture where "b_IsLastPassFlag" = 1 as well as the row prior to that as I'm only interested in the last two rows for every unique "c_PieceID".  So for the below data I'd like the new column to be 0 for all rows except for the ones I've highlighted in yellow and green.

[TMoose · Accepted solution]

That works to create a table as seen.  I'm trying now to implement into the data set which includes much more than what's shown, and of course is from my local source.  Below is what it looks like.  I'm getting an error "Expression.Error: The column 'i_PieceIndex' of the table wasn't found."   Any thoughts as to why my implementation isn't working?

let
Source = Sql.Databases("cml2sqlsrv01"),
Production_DW_RM = Source{[Name="Production_DW_RM"]}[Data],
dbo_r_Chart_Exit = Production_DW_RM{[Schema="dbo",Item="r_Chart_Exit"]}[Data],
#"Sorted Rows" = Table.Sort(dbo_r_Chart_Exit_Gauge_Abs,{{"i_PieceIndex", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"i_PieceIndex", Int64.Type}, {"c_PieceID", Int64.Type}, {"i_PassNumber", Int64.Type}, {"b_ItsLastPassFlag", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"c_PieceID"}, {{"_nestedTable", each _, type table [i_PieceIndex=nullable number, c_PieceID=nullable number, i_PassNumber=nullable number, b_ItsLastPassFlag=nullable number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.AddColumn(_, "newColumn", (x)=> if x[i_PassNumber] = List.Max([i_PassNumber]) or x[i_PassNumber] = List.Max([i_PassNumber]) - 1 then 1 else 0)}}),
#"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"i_PieceIndex", "i_PassNumber", "b_ItsLastPassFlag", "newColumn"}, {"i_PieceIndex", "i_PassNumber", "b_ItsLastPassFlag", "newColumn"})
in
#"Expanded _nestedTable"

[ryan_mayu]

@TMoose 

you can also try this

Column = if('Table'[i_passnumber]+1=maxx(FILTER('Table','Table'[c_pieceid]=EARLIER('Table'[c_pieceid])&&'Table'[i-pieceindex]=EARLIER('Table'[i-pieceindex])+1),'Table'[i_passnumber]),0,1)

pls see the attachment below

[TMoose · Accepted solution]

That works to create a table as seen.  I'm trying now to implement into the data set which includes much more than what's shown, and of course is from my local source.  Below is what it looks like.  I'm getting an error "Expression.Error: The column 'i_PieceIndex' of the table wasn't found."   Any thoughts as to why my implementation isn't working?

let
Source = Sql.Databases("cml2sqlsrv01"),
Production_DW_RM = Source{[Name="Production_DW_RM"]}[Data],
dbo_r_Chart_Exit = Production_DW_RM{[Schema="dbo",Item="r_Chart_Exit"]}[Data],
#"Sorted Rows" = Table.Sort(dbo_r_Chart_Exit_Gauge_Abs,{{"i_PieceIndex", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"i_PieceIndex", Int64.Type}, {"c_PieceID", Int64.Type}, {"i_PassNumber", Int64.Type}, {"b_ItsLastPassFlag", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"c_PieceID"}, {{"_nestedTable", each _, type table [i_PieceIndex=nullable number, c_PieceID=nullable number, i_PassNumber=nullable number, b_ItsLastPassFlag=nullable number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.AddColumn(_, "newColumn", (x)=> if x[i_PassNumber] = List.Max([i_PassNumber]) or x[i_PassNumber] = List.Max([i_PassNumber]) - 1 then 1 else 0)}}),
#"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"i_PieceIndex", "i_PassNumber", "b_ItsLastPassFlag", "newColumn"}, {"i_PieceIndex", "i_PassNumber", "b_ItsLastPassFlag", "newColumn"})
in
#"Expanded _nestedTable"

[jgeddes · Accepted solution]

Typically, but not always, steps in power query refer to the previous step as the reference for the current step.

In the code you attached my #"Changed Type" step was referring to the Source step. In your application you should change the Source to #"Sorted Rows". That will refer to your previous step. 
Now this all assumes that the column names that were in your initial screen shot are the column names that are in your actual dataset. 

If your dataset has different column names then the code will have to be amended to include the column names in your dataset.

[jgeddes]

You can group by c-PieceID, selecting all rows as the aggregate. From there you can add a custom column that puts 1 for every row in the resulting tables that has the max i_PassNumber or the max i_PassNumber - 1. 
Here is an example code you can paste into a blank query's advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BDsAgCATAv3j2gCAIbzH9/zda28aEjUeYsFnmLB6DvZVa2L2ZCRE9w1pQuervjM7bebmgS77v6D27ouvbYbttt2O/gc7ZHV1yfmzXU34Qes6Php7/D0bP/4ega/aObl//6wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [i_PieceIndex = _t, c_PieceID = _t, i_PassNumber = _t, b_ItsLastPassFlag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"i_PieceIndex", Int64.Type}, {"c_PieceID", Int64.Type}, {"i_PassNumber", Int64.Type}, {"b_ItsLastPassFlag", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"c_PieceID"}, {{"_nestedTable", each _, type table [i_PieceIndex=nullable number, c_PieceID=nullable number, i_PassNumber=nullable number, b_ItsLastPassFlag=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.AddColumn(_, "newColumn", (x)=> if x[i_PassNumber] = List.Max([i_PassNumber]) or x[i_PassNumber] = List.Max([i_PassNumber]) - 1 then 1 else 0)}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"i_PieceIndex", "i_PassNumber", "b_ItsLastPassFlag", "newColumn"}, {"i_PieceIndex", "i_PassNumber", "b_ItsLastPassFlag", "newColumn"})
in
    #"Expanded _nestedTable"

Hope this gets you pointed in the right direction.