Forum Discussion

deanbland's avatar
deanbland
Helper III
5 years ago
Solved

How to remove rows below a certain text value.

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?   ...
Need Help
Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

chat_peters's avatar
chat_peters
Helper III
4 years ago

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)