Delete Bottom N rows based on criteria/condition

[Anonymous]

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?

[watkinnc]

Although for the second scenario, @edhans your technique is better suited.

—Nate

[watkinnc]

@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

[watkinnc]

Actually, it's even easier:

= Table.RemoveLastN(PriorStep, each Text.StartsWith([ColumnName], "ABCD"))

This utilizes the optional parameter countOrCondition to determine how many rows to remove.

---Nate

[edhans]

@watkinnc - 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
    Custom1

The 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
    Custom1

will 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.

[AlB · Accepted solution]

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
    res 

Please 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]

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
    Custom1

turns 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.