How to delete all lines except the one I need

[DeepthiAG]

Hi All,

I am having 65000 line items of invoices, line number, vendor name, amount and I want to keep only top 30 vendors(which has highest amount) & want to delete rest of 700 vendors. Sorting by spend do not work due to invoice line number. I have attached the example file if helps (in my original file I have too many information like PO number, Business unit, function etc).

Really appreciate if anyone knows how to keep only top 30 supplier.

Thanks,
Deepthi

 

[AlanSidman]

With your cursor highlight row1
Go to Data-->Filter
On the Invoice Amount click on the Drop Down
Select Number Filters
Select Top 10
Change 10 to 30
Highlight your results
Copy and Paste to a new sheet.

 

[DeepthiAG]

With your cursor highlight row1
Go to Data-->Filter
On the Invoice Amount click on the Drop Down
Select Number Filters
Select Top 10
Change 10 to 30
Highlight your results
Copy and Paste to a new sheet.

Thanks Alan for looking into this. Apologises, I didnt explain well. I am looking for top 30 vendors not top 30 invoice amount.
If I do pivot table, I get top 30 vendors by amount. I want to retain those top 30 vendors delete rest all the lines.
I need this to analyse spend pattern for these vendors.

 

[AlanSidman]

In power query, here is Mcode and file to review

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Format = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Invoice Number", type number}, {"Line Number", Int64.Type}, {"Invoice Amount", type number}, {"Date", type datetime}}),
    RemoveColumns = Table.RemoveColumns(Format,{"Invoice Number", "Line Number", "Date"}),
    GroupSuppiers = Table.Group(RemoveColumns, {"Supplier"}, {{"Total", each List.Sum([Invoice Amount]), type number}}),
    SortedRows = Table.Sort(GroupSuppiers,{{"Total", Order.Descending}}),
    AddIndexColumn = Table.AddIndexColumn(SortedRows, "Index", 0, 1),
    FindTop30 = Table.SelectRows(AddIndexColumn, each [Index] < 31),
    RemoveHelper = Table.RemoveColumns(FindTop30,{"Index"})
in
    RemoveHelper