Forum Discussion
Delete Bottom N rows based on criteria/condition
Hi, I'm using get data from folder, because I've got a multpiple csv file. All data transformation are made on sample file. I need one aditional step and I need an advice..
I need to delete BOTTOM N rows if condition is meet.
Here's my pseudocode:
If LASTROW in column [X] start with "XXX" then delete bottom N rows.
or another criteria
If ROW (LASTROW-1) in column [X] value == "XXX" then delete bottom N rows.
How can I write this in M?
Hi Anonymous
Try this, where PreviousStep is the current last step in your query
last2_ = List.LastN(PreviousStep[X], 2), res= if Text.Start(last2_{0}, 3) ="XXX" or Text.Start(last2_{1}, 3) ="XXX" then Table.RemoveLastN( PreviousStep, N) else PreviousStep in resPlease mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
6 Replies
edhans The second parameter is countOrCondition. As the documentation tells us, if the parameter is a number, that many rows are removed from the bottom. If a condition is specified then starting at the last row, if the last row meets the condition, that row is removed, then the next to last, and on until the condition is no longer true:
You can specify any condition, like Table.RemoveLastN(Table, each [Column1] = “XXX” and each not Text.StartsWith([Column2], “ABC”).
So for the question in play, this would indeed do the trick!
—Nate
- AlB
Community Champion
Hi Anonymous
Try this, where PreviousStep is the current last step in your query
last2_ = List.LastN(PreviousStep[X], 2), res= if Text.Start(last2_{0}, 3) ="XXX" or Text.Start(last2_{1}, 3) ="XXX" then Table.RemoveLastN( PreviousStep, N) else PreviousStep in resPlease mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- edhans
Anonymous - that only works if the last row is XXX. It won't remove the XXX and all subsequent rows.
If Anonymous wants to only remove the last N rows if the last row is "XXX" then this will work - similar logic as before, but XXX is in the last row in my sample data. The last 5 rows get removed if that is the case.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagsmIiIhIMCOxOCUNB8PIyAimVik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), varXXXPosition = List.PositionOf(Source[Column1], "XXX") + 1, varTotalRows = Table.RowCount(Source), Custom1 = if varXXXPosition = varTotalRows then Table.RemoveLastN(Source, 5) else Source in Custom1The optional condition is nice, but it seems to be only really useful on numeric data, for example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLCMwyBrKMwSwTIMsEzDIFskzBrIiICCDbDMxOLE5JA3LMkTkWyBxLiNFGIMMNDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", Int64.Type}}), Custom1 = Table.RemoveLastN(#"Changed Type", each [Column2] > 3) in Custom1will remove all rows where Column2 is 4 or higher.
A text comparison seems to return true/false, and true is 1, so it removes the last row.
Unless there is something I am missing.
- edhans
Hi Anonymous - see this blog post I wrote a few weeks ago. It will show you exactly how to do that. It was for finding the first row, but you can use it to find the last row. All you will need to do is:
- Find the XXX per the blog post
- Count the total rows in your data set
- Then use Table.RemoveLastN() for the difference between the total rows and the location of the XXX row.
Here is some actual code to show you:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagsmIiAgwnVickoaDYWQE1BwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), varXXXPosition = List.PositionOf(Source[Column1], "XXX"), varTotalRows = Table.RowCount(Source), Custom1 = Table.RemoveLastN(Source, varTotalRows - varXXXPosition) in Custom1turns this:
into this:
But read the blog linked to above as it will walk you through the logic of it.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model. Although for the second scenario, edhans your technique is better suited.
—Nate
