Forum Discussion

Maltaparis's avatar
Maltaparis
Frequent Visitor
1 year ago
Solved

Need help to fix my code

Dear all, I need your help I am getting crazy, in Powerquery, I created a calculated column but it doesnt worK All the column names are correct It keep stating there is a syntax error with "and" ...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    There is no need for parentheses here after if .  

     

    But you are missing the reference to the outer scope. 

    each [MAT] = [MAT]

    will always evaluate to true and 

    and [Date] = [DateMax]

    doesn't know where to find [Date].

     

    Show the entire code for that step if you want further help.

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

The problem is related to each _ syntax. when you add a new column it will automaticly add each at the begining of the third argument (place you write the formula,) so in your formula you can not use each _ again becuse the variables (_)  will be ,mixed, so replace each _ by new variable name like (x)=> and rewrite the formula as below


if ([Date] = [DateMin] and 
    Table.RowCount(Table.SelectRows(MS, (x)=> x[MAT] = _[MAT] and x[Date] = _[DateMax])) > 0) then "Présent"
else if ([Date] = [DateMax] and 
         Table.RowCount(Table.SelectRows(MS, (x)=> x[MAT] = _[MAT] and x[Date] = _[DateMin])) = 0) then "Nouveau"
else if ([Date] <> [DateMax] and 
         Table.RowCount(Table.SelectRows(MS, (x)=> x[MAT] = _[MAT] and x[Date] = _[DateMax])) = 0) then "Départ"
else "Autre"