Forum Discussion
Conditional New Column To Filter Data
- 2 years ago
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" - 2 years ago
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.
you can also try this