Need help to fix my code

[Maltaparis]

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" or with the column DATE if I dont use ([Date]) bur rather [Date] 

Date is also in a date format

Can someo,ne help me?

Thank u all fr you help

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

[Maltaparis]

I also Add, MS is the name I gve to my table ,

[Maltaparis]

Dear all,

First of all thank you very much for your heklp

To answer some of the question

My code is complete

I have so far a ingle Table called 'MS", with a column Date (in date format), I dont understand why the code is acting like it doesnt exist?

[Anonymous]

HI @Maltaparis ,

I noticed that 'MS' appears in the first parameter of Table.SelectedRows.

Best Regards,

Bof

[lbendlin]

My code is complete

If that were the case you wouldn't have the errors. 

I dont understand why the code is acting like it doesnt exist?

That's not the problem.  You need to reference the individual steps in your process, and you need to take special care of nested scopes.

I have so far a single Table called 'MS"

That's the name of the final output.  Each applied step has a different name.

Please show the entire code.

[ZhangKun]

The reason for the error is that [Date] in Table.SelectRows does not exist in the MS table.
There is also a semantic error where the expression "[MAT] = [MAT]" is always true.
Take some of them as examples (please modify according to the actual content):

//Table.RowCount(Table.SelectRows(MS, each [MAT] = [MAT] and [Date] = [DateMax]))
Table.RowCount(Table.SelectRows(MS, (r) => x[MAT] = [MAT] and x[Date] = [DateMax]))

A prefix with "r" indicates that it is from an MS table, and a prefix with no "r" indicates that it is from an external table (a table to which columns are added).

[Omid_Motamedise]

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"

[lbendlin]

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.