+ Reply to Thread
Results 1 to 5 of 5

Power Query Table Group , help with Custom Comparer theory

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

    Power Query Table Group , help with Custom Comparer theory

    Hi,

    I recently came acrross a formula to group by a sum value ;

    Table.Group custom comparer ;

    I sort of get that in custom group (x,y)=> each time a true is returned a new group is started so ;

    Please Login or Register  to view this content.

    But I recently found this formula and can't figure it out;

    Please Login or Register  to view this content.
    so this will mean that the sum of units for each group < 50 , but I can't figure how the count for lList.Range , is working
    so index x and y x = current y = next ?
    so 2 - 1 + 1 = 2, 3 - 2 + 1 = 2 , 4 - 3 + 1 = 2, but if 2 is used the formula breaks so this cannot be what is happening ? All I can guess is that
    the y value increases / iterates through until the condition is met, 2 - 1 +1 , 3 - 1 + 1, 4 -1 + 1 etc. ?

    if anyone can help in explaining how this is working please do , I have read ( i think most of )
    PQ how on this but it did not really help this, unless i missed that bit.

    I have attached a work book with examples.

    Richard.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: Power Query Table Group , help with Custom Comparer theory

    It's comparer function. And since GroupKind.Type in Table.Group() argument is specified as 0 (GroupKind.Local).
    So when iterated over the table, x is offset from start of table, meaning Index value of first row of group.
    So starts from 0.
    Then List.Range() iterates over list using List's index. y[Index] - x[Index] + 1 to supply Count from Offset. When it satisfies condition of List.Sum() > 50. It starts new at new x value.

    But it is hard to truly understand or explain what goes on under the hood as comparer functions operate quite differently from standard use of functions.
    See link for example.
    https://www.sumproduct.com/blog/arti...r-table-groups
    "Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
    ― Robert A. Heinlein

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,883

    Re: Power Query Table Group , help with Custom Comparer theory

    It's >50 not <50.

    x is the starting point, then it loops until the sum of dex[Unit] from the starting point to the current iteration + 1 is > 50, then it resets x and starts again. It's looking at a section of the list dex[Unit] starting at x[Index] and extending for a count of y[Index]-x[Index]+1. So at the start the x[Index] is 0 and it checks the next Index (1) and sees if the portion of the list starting from item 0 for a count of (1-0)+1 (i.e. 2) items is greater than 50. If so, it creates a group, if not, it checks the next year[Index] (so it will count 3) and so on.

    Does that make sense?
    Everyone who confuses correlation and causation ends up dead.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: Power Query Table Group , help with Custom Comparer theory

    @romperstomper

    Actually >50 is the condition you need here. <50 will generate group for each row in this case. As this type of comparer function use requires GroupKind.Local.

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

    Re: Power Query Table Group , help with Custom Comparer theory

    Thank you both, that's sort of what I'd though must be the answer, not that I'm really sure, as said my initial thinking for the count was that it
    would always = 2, but this was clearly not the case. regarding group kind, I did try a global group but it returned out of range,
    which I think is looking for 12 in a list of 1 to 10.
    Have not had a chance to look at the link but will do.

    Thanks again.

    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. [SOLVED] Power Query custom function variable to make table a list
    By Dicken in forum Office 365
    Replies: 10
    Last Post: 07-08-2023, 11:13 AM
  2. [SOLVED] Power Query Group and select rows within group
    By Dicken in forum Office 365
    Replies: 2
    Last Post: 06-10-2023, 07:51 AM
  3. Add Custom column of its own query name in power query
    By ks.Prajnan in forum Excel General
    Replies: 3
    Last Post: 07-27-2022, 09:24 AM
  4. Power query Custom function / parameter query
    By Dicken in forum Office 365
    Replies: 0
    Last Post: 03-23-2022, 10:41 AM
  5. Replies: 6
    Last Post: 08-24-2020, 12:34 PM
  6. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  7. Add custom fields to a table to simulate a pivot table - Power Query
    By nathalielesperance in forum Excel General
    Replies: 2
    Last Post: 10-25-2018, 06:44 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