Forum Discussion

rdnguyen's avatar
rdnguyen
Helper V
4 years ago
Solved

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

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.
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago
    "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)=>

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    "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)=>