+ Reply to Thread
Results 1 to 3 of 3

Power Query ranking multiple columns

  1. #1
    Forum Contributor
    Join Date
    07-18-2014
    Location
    Missouri, USA
    MS-Off Ver
    2010
    Posts
    175

    Power Query ranking multiple columns

    I am using power query to pull in around 20 columns of data. One of the columns is a players name. The other columns are various seasonal stats for that player. Instead of the raw stats which is currently in each column, I want the person leading each stat to have a 1 in that column. The player that has the second best statistic in that column will have a 2..........
    So, in the end, instead of seeing the actual stat in the 20 total columns, I will see the players name in the first column and their ranking in each of the following columns.

    Is there an easy way to accomplish this in power query?

    Now:
    Name Avg OBA BB K%
    Smith .234 .321 .23 .13
    Lane .221 .275 .25 .15
    Frank .300 .145 .20 .14

    End Result:
    Name Avg OBA BB K%
    Smith 2 1 2 3
    Lane 3 2 1 1
    Frank 1 3 3 2

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Power Query ranking multiple columns

    Here's one approach...

    With your sample data in an Excel Table named Table1:
    Please Login or Register  to view this content.
    Connect that table to Power Query.

    This M-Code in a query named: GetStatRanks
    calculates the ranks
    Please Login or Register  to view this content.
    These are the results:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    07-18-2014
    Location
    Missouri, USA
    MS-Off Ver
    2010
    Posts
    175

    Re: Power Query ranking multiple columns

    I can work with this. Thank you!

+ 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. Replies: 1
    Last Post: 03-12-2019, 04:42 PM
  2. 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
  3. [SOLVED] Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1
    By jekeith in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2018, 10:31 AM
  4. [SOLVED] Changing Power Query rows to columns on repeat cells.
    By beaglesBuddy in forum Excel General
    Replies: 3
    Last Post: 06-14-2017, 02:13 PM
  5. Unpivot in Power Query keeping two columns
    By PennyK in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-10-2017, 06:27 AM
  6. Replies: 0
    Last Post: 10-25-2016, 02:59 AM
  7. How to remove null values from several columns with Power Query?
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2016, 11:08 AM

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