Forum Discussion

deanbland's avatar
deanbland
Icon for Helper III rankHelper 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

deanbland's avatar
deanbland
Icon for Helper III rankHelper III
5 years ago

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

 

Thanks

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

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