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

PhilipTreacy's avatar
PhilipTreacy
Super User
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
    Helper III
    5 years ago

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

     

    Thanks

    • PhilipTreacy's avatar
      PhilipTreacy
      Super 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