Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
9 months ago
Solved

Tidying excel spreadsheet in power query

Hi, I have excel file that has multiple tables in same sheet.
It has some other tables that I do not need, but there is not easy way to remove them since size of the tables can change every week so I cant just remove certain amount of rows from the bottom.

But one thing is consistent that whole area that I do not need is after and including row that has TOTAT STD & TOTAL STD2.
If it is possible some how to to use that to delete rows that have TOTAT STD and rows after that I would apreciate the help.

Thanks

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    9 months ago

    I believe this task can be done easily with a simple VBA script. Attach it to a button for ease of use, if necessary.

     

    The VBA Range.Find method will locate the row that contains TOTAT STD & TOTAL STD2.

    Then just delete the rows (or the contents of the rows) below that.

     

    If you want the desired table on a new sheet, then use the Range.Copy method to copy the rows up to and including the found row to the new sheet.

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    9 months ago

    I believe this task can be done easily with a simple VBA script. Attach it to a button for ease of use, if necessary.

     

    The VBA Range.Find method will locate the row that contains TOTAT STD & TOTAL STD2.

    Then just delete the rows (or the contents of the rows) below that.

     

    If you want the desired table on a new sheet, then use the Range.Copy method to copy the rows up to and including the found row to the new sheet.

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    9 months ago

    Hi Justas4478 

     

    Your request isn't very clear. and without seeing your file it's hard to know exactly how your data is organised.

     

    If you have a table you don't want, then delete it?  Or am I missing some other detail?

     

    As for those celsl that have the heading TOTAT STD, they are not in a table, I don't understand why you can't just delete those too.

     

    Where does Power Query come  in?

     

    Please supply your file.

     

    Phil

     

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    9 months ago

    Hi Justas4478,

    Give this a go. Create a new blank query, name it: findFinalRow
    Open the advanced editor for findFinalRow, select everything that is there and replace it with this:

    (input, match) as logical =>
    Comparer.Equals( Comparer.OrdinalIgnoreCase,Text.From(input ?? ""), match)

     

    Go back to the query with the data you brought in from the worksheet, make sure the last step is selected in the Applied Steps pane. Press the fx in front of the formula bar, this will insert a manual step into your query and show the identifier of the previous step from that query.

    This is a bit tricky but copy this code and paste it inbetween the equals sign (=) and that identifier.

    Table.RemoveLastN(
    Table.Skip( PutYourIdentifierHere, 
        each List.AnyTrue(
            List.Transform(
                Record.ToList(_), 
                (x)=> findFinalRow(x, "TOTAT STD"))
        )
    )
)

    Your identifier can now be found after the final closing parenthesis of this expression, select and cut it. Next paste it in place of PutYourIdentifierHere.

    I hope this is helpful.