Forum Discussion

TMoose's avatar
TMoose
New Member
2 years ago
Solved

Conditional New Column To Filter Data

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 ...
  • TMoose's avatar
    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"

  • jgeddes's avatar
    jgeddes
    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.