Help in Query Editor with the second argument of Table.FirstN using error criteria.

[AndreLuiz]

Hello,
I would like to use the second argument of Table.FirstN with a condition in which the function stop evaluating when an error is found.
I've tried Table.FirstN(Table, each [Column1] ; [Column1] <> "Error") but it does't work..

Any help will be appreciated!

[Anonymous · Accepted solution]

The 2nd parameter of Table.FirstN() needs to be an expression that evaluates to a whole number.

BUT, you can do something like this.

PreviousStep = //some M code here,  
Table_with_FirstN = Table.FirstN(PreviousStep, 20),
Conditional = //some M code here that says whether or not to apply the FirstN transformation.
FinalTable = if Conditional = true then Table_with_FirstN else PreviousStep 
in

FinalTable 

[Anonymous · Accepted solution]

The 2nd parameter of Table.FirstN() needs to be an expression that evaluates to a whole number.

BUT, you can do something like this.

PreviousStep = //some M code here,  
Table_with_FirstN = Table.FirstN(PreviousStep, 20),
Conditional = //some M code here that says whether or not to apply the FirstN transformation.
FinalTable = if Conditional = true then Table_with_FirstN else PreviousStep 
in

FinalTable 

[AndreLuiz · Accepted solution]

Thank you for the answer Chris!

What I liked in your anwer is the simplicity and brevity in the M code.

In following the brevity approach to the problem I've found that the 2nd parameter of Table.FirstN() can be any condition which evaluates to True/False. So, by trying to catch the error condition, I've came to the following solution:

//Using the "try ... otherwise" clause in M
//A cell is equal to itself unless it contains an Error

SingleStep = Table.FirstN(Table, each try [Column1] = [Column1] otherwise null <> null)
//                                    (       try..otherwise error test       )

Greetings,

André