How to remove rows below a certain text value.

[deanbland]

Hi, 

I have a dataset that has a constantly changing number of rows. I am wanting to remove all rows below a certain cell that will always have the same text value. Is there a way to do this? 

Below is an example of the column I am referring to. I am wanting to remove everything below YTD, but as you can see, the column includes the same accounts above and below this. 

Forecast

Account 1

Account 2

Account 3

YTD

Account 1

Account 2

Account 3

Thank you in advance! 

Dean 

[chat_peters]

Hi This thread was extremely useful. I have a question

1) What if I want to delete everything above the YTD Position, how can I tweak this formula?

RemoveRows = Table.RemoveRows(Source,YTD_Pos-1,Table.RowCount(Source)-YTD_Pos+1)

[PhilipTreacy · Accepted solution]

Hi @deanbland 

This Power Query code works - download my example PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSk1OLC5RitWJVnJMTs4vzStRMEThGaHwjME8MBEZ4kKKtlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    YTD_Pos = List.PositionOf(Table.Column(Source,"Column1"),"YTD"),
    RemoveRows = Table.RemoveRows(Source,YTD_Pos-1,Table.RowCount(Source)-YTD_Pos+1)
in
    RemoveRows

Using this sample data

This sample code requires that the column containing YTD is named Column1 so you'll need to change that to suit your table.

Regards

Phil

[deanbland]

Hi, how would I tweak this to remove everything above YTD?

Thanks

[PhilipTreacy]

Hi @deanbland 

You can use Table.Skip for this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSk1OLC5RitWJVnJMTs4vzStRMEThGaHwjME8MBEZ4kKKtlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    YTD_Pos = List.PositionOf(Table.Column(Source,"Column1"),"YTD"),
    RemoveRows = Table.Skip(Source,YTD_Pos)
in
    RemoveRows

Regards

Phil