+ Reply to Thread
Results 1 to 3 of 3

Power Query Group and select rows within group

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    761

    Power Query Group and select rows within group

    Hi,

    I've been trying to retrieve the records were the units values is in the top 3 , to do this I first grouped the data in this case by month
    and sorted the groups descending, within I then used ;

    Please Login or Register  to view this content.
    to get top 3rd highest distinct value and {0} to get the value

    I then wanted to use this a criteria for Table.SelectRows but can't figure out how to access it.

    This is how i first trie, Just put '10' to get to work, but could not use the [TestA] vlaue,

    Please Login or Register  to view this content.
    I then tried to add the criter into the grouping ;

    Please Login or Register  to view this content.

    Any suggestion as to how to access a value created in the grouping, I have thought of add columns and using a let statement?

    Update ; the got to a solution whihc is as follows ; within the group add column of for example 3rd highest unit , listrange ( {} , 2 ) , then again within the grouping
    use table select rows

    Please Login or Register  to view this content.
    Updated file attached ; query ; result

    RD
    Attached Files Attached Files
    Last edited by Dicken; 06-13-2023 at 12:16 PM. Reason: Solved

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2502 Win 11
    Posts
    24,601

    Re: Power Query Group and select rows within group

    I am confused by your request. What is the final result you are looking for. If you are looking for the top three for each month then the following should work for you. If something else, then you need to explain clearly what you are looking for a result (mocked up) in your explanation. You have three scenarios in your workbook. Don't know if this is what you want or what you need.

    Please Login or Register  to view this content.
    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
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    761

    Re: Power Query Group and select rows within group

    I was trying to return the sum of the top 3 values for each grouping, so eg 12, 12, 10,10,9,8,6 would return 12,12,10,10,9
    and then sum , and concatenate the for example customers associated with those sales, so

    Please Login or Register  to view this content.
    but , see 'finished' I was trying to get it in the original grouping, ' count' column, thanks for the help, I'll mark this up as closed as I'm pretty close to getting what I wanted,

    RD

+ 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. Group by in Power Query and get unique values
    By Dicken in forum Office 365
    Replies: 1
    Last Post: 08-14-2022, 10:15 AM
  2. [SOLVED] Power Query - Multiple Rankings by Single Group
    By arpod in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-12-2021, 09:37 PM
  3. [SOLVED] Power Query - add empty rows & rows of text below bottom of group
    By josephteh in forum Excel General
    Replies: 1
    Last Post: 11-16-2020, 02:59 PM
  4. [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
  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: 14
    Last Post: 01-12-2017, 01:41 PM
  7. Replies: 1
    Last Post: 02-10-2014, 10:26 PM

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