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 🙂
I applied the formula in the following script but got the error message:
Expression.Error: A cyclic reference was encountered during evaluation.
What’s wrong with it?
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type datetime}, {“Qty”, Int64.Type}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each Table.RemoveFirstN(Table1,each _[Qty] = null))
in
#”Added Custom”
I think that what you want would be this:
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type datetime}, {“Qty”, Int64.Type}}),
RemoveFirstRows = Table.RemoveFirstN(#”Changed Type”,each _[Qty] = null)
in
RemoveFirstRows
It did nothing at all, just copied original table without any changes?
Maybe this then:
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type datetime}, {“Qty”, Int64.Type}}),
RemoveFirstRows = Table.RemoveFirstN(#”Changed Type”,each (_[Qty] = null or _[Qty] = “”))
in
RemoveFirstRows
It depends on whether your “blanks” are null (which is the case when importing from an Excel-table) or “just blank”, which sometimes comes from other sources, this would then be expressed by “”. (I’ve edited the code above in the article as well in order to cover theses cases)
Neither null nor “” could remove the target rows!
Then there will be sth other in those cells. You can click into one of them and select the content by doubleclicking and copy.
Or it might be non-printable characters in them which you can remove by cleaning (Transform -> Text Column -> Format -> Clean) Also a Trim there might help (if it is OK that it will be applied to the whole table).
I checked again the following script step by step
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type datetime}, {“Qty”, Int64.Type}}),
#”Filtered Rows1″ = Table.SelectRows(#”Changed Type”, each ([Qty] null)),
RemoveFirstRows = Table.RemoveFirstN(#”Filtered Rows1″,each (_[Qty] = null or _[Qty] = “”))
in
RemoveFirstRows
The result of step #”Changed Type” returned 6 nulls => no non-printable characters inside, right?
I added step #”Filtered Rows1″ to filter those 6 rows out without problems.
My source table is quite simple, details as follows:
Date Qty
1/1 500
1/2 800
1/3 2000
1/4 2500
1/5 1200
1/6 800
1/7
1/8 2500
1/9 1200
1/10 800
1/11 800
1/12 2000
1/13
1/14 1200
1/15 800
1/16
1/17 600
1/18
1/19
1/20 500
1/21 800
1/22 2000
1/23 2500
1/24
Probably I should send the file to you for further cheking at your side?!
I’m afraid to say that the code does exactly what I would expect it to do. It would only remove anything from your table if there were blank rows at the start of your table/in the very first rows. But as your first row is not empty, it will not remove any FirstNs which are empty.
To get a feeling for what this function does, you can take this:
RemoveFirstRows = Table.RemoveFirstN(#”Changed Type”,each _[Qty] <2000)
and it should remove the first 2 rows, as they are all below 2000 and the 3rd row breaks this criterium. So then the evaluation will stop.
While RemoveFirstRows = Table.RemoveFirstN(#”Changed Type”,each _[Qty] <2500)
should remove the first 3 rows, as the 4th row breaks the criterium.
Yes, You are right, if I sorted the column Qty ascending first then all the null values would be removed accordingly.Thanks for your explanation. I got your point finally.
Dear Imke,
List.FirstN is missing.
Regards,
Marcel
Hi Marcel,
you’re right – thx for the sharp eye 😉