Tidying excel spreadsheet in power query

[Justas4478]

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

[m_dekorte]

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.

[PhilipTreacy]

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

[ronrsnfld · Accepted solution]

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.