Error "We cannot convert a value of type Function to type Logical"

[rdnguyen]

Hi All,

I have this SQL query that I tried to convert to Power Query

SELECT  labID,
(Select COUNT(*) from tblRouting

          where JobNo = tblLabor.jobno and operno <= tblLabor.operno) as OpCount,
(Select SUM(fuprodtime * foperqty  + fsetuptime + fmovetime) from tblRouting

          where JobNo = tblLabor.jobno and operno <= tblLabor.operno) as CompHrs
FROM tblLabor

converted to power query as following:

let

  Source = tblLabor,

  #"Choose columns" = Table.SelectColumns(Source, {"jobno", "operno"}),

  #"Removed duplicates" = Table.Distinct(#"Choose columns", {"jobno", "operno"}),

  #"Merged queries" = Table.NestedJoin(#"Removed duplicates", {"jobno"}, #"tblRouting", {"jobno"}, "jr", JoinKind.LeftOuter),

  #"Added custom" = Table.AddColumn(#"Merged queries", "Custom", each Table.SelectRows(Table.Buffer([jr]),each (jr)=> jr[operno] <= [operno] )),

  #"Expanded Custom" = Table.ExpandTableColumn(#"Added custom", "Custom", {"operno", "Hours"}, {"operno.1", "Hours"})

in

  #"Expanded Custom"

So what I did is perform Left outer join of the two table matching jobno then add custom Column using SelectRows on the nested table to filter down those operno not matching criteria. 

Kindly help me on this.

[rdnguyen]

Thanks Rocco,

that worked.

[Anonymous · Accepted solution]

"Added custom" = Table.AddColumn(#"Merged queries", "Custom", each Table.SelectRows(Table.Buffer([jr]),each (jr)=> jr[operno] <= [operno] )),

"Added custom" = Table.AddColumn(#"Merged queries", "Custom", each Table.SelectRows(Table.Buffer([jr]),(jr)=> jr[operno] <= [operno] )),

try to cut each on front of (jr)=>