Today I discovered that we can use conditions in many of the N-selecting functions where one/I would normally expect just a number-expression for the N:
Table.RemoveFirstN( table as table, optional countOrCondition as any)
So apart from being able to select a certain number of rows to be removed, we can pass a condition (as function). This condition will iteratively be checked for every row in the table (from top or bottom) and as long as every (next) step returns true, the resulting range will be removed. So as soon as one row breaks the condition, the process will stop.
I find that totally awesome, as we can now remove all top-rows who have an empty field in Column3 like this for example:
Table.RemoveFirstN(<MyTable>, each each (_[Column3] = null or _[Column3] = “”))
Yes, this will remove the first sequence of consecutive nulls in the table. So all other rows with nulls in the table coming later after a non-null value has “broken in”, will remain.
This is the list of function, where you can use this M-agic:
Enjoy & stay queryious 🙂