+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

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

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,735

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

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    69

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

    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!
    If you like my answer please *Add Reputation

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkey
    MS-Off Ver
    Office 2010 - 32 Bit
    Posts
    658

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

    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.
    Last edited by Haluk; 07-16-2020 at 05:45 AM.

  5. #5
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkey
    MS-Off Ver
    Office 2010 - 32 Bit
    Posts
    658

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

    @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...
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,637

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

    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.
    Attached Files Attached Files
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  2. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. Replies: 7
    Last Post: 04-24-2018, 12:07 AM
  5. [SOLVED] Power Query: Group by category, list text items on separate rows
    By JimDandy in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-05-2017, 12:11 AM
  6. Replies: 0
    Last Post: 04-09-2014, 04:21 PM
  7. Replies: 1
    Last Post: 04-07-2014, 01:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1