Forum Discussion
Post ProdigyTidying excel spreadsheet in power query
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.
Resident RockstarHi 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.