Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 BOTTO...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

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