Forum Discussion
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" 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"
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.
7 Replies
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.
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"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).
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?
My code is completeIf 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.
HI Maltaparis ,
I noticed that 'MS' appears in the first parameter of Table.SelectedRows.
Best Regards,
Bof
I also Add, MS is the name I gve to my table ,
