Power Query for identifying the top 5 vendors by category and the top 70% of items

[chandramouliarun]

Hi All,

Hope you are doing well!...I am trying to write a power query to identify the top 5 vendors for the categories Backorder and Pre-sell according to the amounts refunded ...Also I am tryig the find the top 70% of the items contribution by Pre-sell and Backorder category..This will list the itemid with the number of times the itemid appears in the list with the refunds sorted in descending order contributing to the top 70% for each individual category Pre-sell and Backorder..

I have all the input data in the main sheet and the required outputs in the next three tabs...Can you please help me with the power queries for the same..



Thanks,
Arun

[JeteMc]

The top 5 vendors is something that a pivot table can do.
Vendor Name in Rows area, Sum of Refunds in Values area and Ticket Reason in Filter area. (modeled on the Top 5 Vendors sheet)
As for the top 70% a pivot table could do a lot of the heavy lifting, although it still requires some manual adjustment.
Item ID in Rows area. Count if Item ID and Sum of Refunds (twice) in Values area. Ticket Reason in Filter area.
The second instance of Sum of Refunds utilizes % Running Total in Item ID. At this point the user would need to look at the value of the Sum of Refunds (first instance) and use a greater than or equal to values filter, in this case filter to >= 4500
Perhaps another contributor could show us both how power query could automate the top 70%
Let us know if you have any questions.

[MushroomFace]

Hi

Attached is a workbook to resolve the Top 5, you can go back into PQ and then filter the index to be <=5, at the moment i have left it without the filter as you don't have more than 5 in each category.

The orange tabs are new or updated.

Regarding the 70% i am not sure, i would look up articles on applying running totals in power query, quick search gives this:
https://www.excelguru.ca/blog/2015/0...n-power-query/

Good luck!

[Haluk]

As an alternative, TOP 5 queries can also be listed on a sheet by VBA - "Microsoft Query"

Note: There is an extra space at the end of the sheet name "Main Sheet ". So, after removing this extra space character;

Please Login or Register  to view this content.

[Haluk]

@chandramouliarun;

The attached file includes the code in my previous message and lists the percentages of "Backorder" and "Pre-Sell" refunds summing up 100% .

The percentages are in descending order from top to bottom, so you can easily detect when the <=70% condition is satisfied just by highlighting the "Percentage" column with your mouse and reading the sum value on the status bar of your Excel window.

If needed, a simple loop can be added to delete the rows when the condition (<=70%)is satisfied.

SQL and "Microsoft Query" is used in the attached sample file...

[CK76]

If using PQ only... Though I'd personally recommend use of PowerPivot & DAX measures.

1. Create custom function "fnTop5" add table name as first argument and reason (i.e. Backorder or Pre-Sell) as text.
Ex:

Please Login or Register  to view this content.

2. Invoke it using GUI and then load resulting tables back onto sheet.
0.JPG

For top 70%... PQ isn't the right tool, but let me see if I can come up with something.