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? 

 

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 

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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper 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
      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

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