Results 1 to 1 of 1

Thread: PQ - Return unique values in row based table

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    239
    Rep Power
    7

    Cool PQ - Return unique values in row based table

    (question from the web)
    Not sure if my Title makes sense, but I'm not sure how else to explain it. I am looking to take the first table (Column A and B) and convert it to return unique values for each unique value in column A. Below is an example and what I am looking the out.

    Data I currently have (real data is 50k+ rows)
    ABC 123
    ABC 123
    ABC 456
    ABC 789
    DEF 123
    DEF 123
    DEF 789
    GHI 1010
    GHI 1010

    Outcome I am looking to get:
    ABC 123 456 789
    DEF 123 789
    GHI 1010

    What is the best approach to do this? I appreciate the help and thank you in advance.
    ColA ColB ColA ColB
    ABC
    123
    ABC 123 456 789
    ABC
    123
    DEF 123 789
    ABC
    456
    GHI 1010
    ABC
    789
    DEF
    123
    DEF
    123
    DEF
    789
    GHI
    1010
    GHI
    1010

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"ColA"}, {{"Count", each _, type table}}),
        List = Table.AddColumn(Group, "ColB", each List.Distinct([Count][ColB])),
        Extract = Table.TransformColumns(List, {"ColB", each Text.Combine(List.Transform(_, Text.From), " "), type text})
    in
        Extract

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=320960#p320960
    https://eileenslounge.com/viewtopic.php?p=320957#p3209573
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-04-2024 at 10:18 PM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. Return a comment based on values in another cell
    By msiyab in forum Excel Help
    Replies: 5
    Last Post: 05-23-2015, 02:47 AM
  2. Replies: 5
    Last Post: 08-10-2013, 04:15 PM
  3. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  4. Group Pivot Data Based On Row Values In One Column
    By mrmmickle1 in forum Excel Help
    Replies: 10
    Last Post: 10-09-2012, 11:46 PM
  5. Replies: 2
    Last Post: 01-07-2012, 12:11 AM

Posting Permissions

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