+ Reply to Thread
Results 1 to 8 of 8

Power Query - Multiple Rankings by Single Group

  1. #1
    Registered User
    Join Date
    03-12-2021
    Location
    London, England
    MS-Off Ver
    16
    Posts
    6

    Power Query - Multiple Rankings by Single Group

    Hi there,

    Relatively new PQ user .. making some decent progress though! One has me stumped.

    I am trying to rank several columns for each given group in Power Query, but understanding how is escaping me.

    Attached are 'before and after' samples to better explain what I'm trying to achieve. If anyone is kind enough to take a look and offer suggestions that would be greatly appreciated.

    Arpod
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Power Query - Multiple Rankings by Single Group

    I split your table into three queries as shown in attached. I then sorted them based upon the Measure for each query and then grouped them. Finally, I merged (joined each). The final query is then closed and loaded to Excel.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    1
    SerialID Custom.Measure1 Measure2.Custom.Measure2 Measure3.Custom.Measure3 Custom.Index Measure2.Custom.Index Measure3.Custom.Index
    2
    176849623
    1.9775
    1.8762
    1.74
    1
    1
    1
    3
    176849623
    4.6138
    10.7409
    10.16
    2
    2
    2
    4
    176849623
    18.0497
    12.2787
    13.39
    3
    3
    3
    5
    176849623
    13.7621
    13.6217
    13.7
    4
    4
    4
    6
    176849623
    14.8173
    10.8251
    14.06
    5
    5
    5
    7
    176849623
    19.5439
    19.4192
    26.7
    6
    6
    6
    8
    176849623
    32.4051
    26.4185
    27.46
    7
    7
    7
    9
    178371835
    2.854
    2.2829
    1.86
    1
    1
    1
    10
    178371835
    3.5941
    11.0347
    4.74
    2
    2
    2
    11
    178371835
    9.7506
    13.6788
    14.51
    3
    3
    3
    12
    178371835
    8.8838
    13.1005
    20.17
    4
    4
    4
    13
    178371835
    18.4198
    19.7243
    28.24
    5
    5
    5
    14
    178371835
    25.4829
    13.3065
    29.04
    6
    6
    6
    15
    178371835
    19.3573
    16.4257
    34.27
    7
    7
    7
    16
    178371835
    98.1993
    22.5481
    57.2
    8
    8
    8
    17
    178371835
    241.4
    17.5675
    92.43
    9
    9
    9
    18
    178371835
    904
    29.5017
    192.24
    10
    10
    10
    Sheet: Merge2
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-12-2021
    Location
    London, England
    MS-Off Ver
    16
    Posts
    6

    Re: Power Query - Multiple Rankings by Single Group

    Thanks so much alansidman.

    I have downloaded and will work through testing / understanding over the next couple of days.

  4. #4
    Registered User
    Join Date
    03-12-2021
    Location
    London, England
    MS-Off Ver
    16
    Posts
    6

    Re: Power Query - Multiple Rankings by Single Group

    Hi alansidman ... apologies in that it has taken me a while to go through this!

    Now understand most of the logic behind it, but it's not exactly doing what I'm after. As an example, in the screenshot above, the "Measure2.Custom.Index" field does not actually rank (index) the "Measure2.Custom.Measure2" in order. Instead, it is just the same rank as the "Custom.Measure1" readings. But in the initial group/index query (Measure2), it does.

    So I am guessing that either a) the merge needs to be adjusted, or b) that I would need to add an additional field alongside the SerialID one (e.g. measurement number) to facilitate a merge that will get the desired outcome. Or I could be missing the point!

    All insights or suggestions welcome and appreciated!

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query - Multiple Rankings by Single Group

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-12-2021
    Location
    London, England
    MS-Off Ver
    16
    Posts
    6

    Re: Power Query - Multiple Rankings by Single Group

    Thanks Bo_Ry ... will give that a try!

  7. #7
    Registered User
    Join Date
    03-12-2021
    Location
    London, England
    MS-Off Ver
    16
    Posts
    6

    Re: Power Query - Multiple Rankings by Single Group

    Hi Bo_Ry, again apologies for the long delay in responding. An upcoming deadline to have this done got things moving again.

    Just wanted to say that I have a working approach using your code suggestion above as the basis. Basically am extracting a query from a database into Excel via PQ, then running a tweaked version of your code on that to provide the required output format. It's all working nicely. So just wanted to provide feedback and say thanks!

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Power Query - Multiple Rankings by Single Group

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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] multiple outputs from single power query
    By siddiqsh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-25-2022, 03:05 AM
  2. [SOLVED] Power Query Group by Each Change in Column Value
    By ibuhary in forum Excel General
    Replies: 9
    Last Post: 12-15-2019, 04:41 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. Multi rows into Single row using power query
    By alipezu in forum Excel General
    Replies: 4
    Last Post: 10-22-2017, 08:02 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. Exporting Facebook group comments to Excel using Power Query?
    By trapperjohn22 in forum Excel General
    Replies: 2
    Last Post: 02-27-2016, 04:12 AM
  7. Replies: 0
    Last Post: 03-20-2013, 11:18 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