finding unique support workers in a data set and then grouping them along side the corresponding

[JonBastians]

Hi, i have a pivot table with child name in the rows and also support worker in rows. What i get is a short list of all the support workers each child has had support sessions with.... but it's all in 1 column . I'd like to create a new table with every child listed in one column, and a single cell with names of their support workers deliminated by comma. I know i could concatenate each short list but i would like a dynamic solution as some children have 6 support workers, others only 1 or 2.  I had some initial success with 

2 formulas i gleened from ChatGPT but now that solutions does not seem to work ( and it was on the data set and not the pivot table)

First made a column with this formula :

It gave me a huge string text-joining every support worker entry for every child... with heaps of duplicates.

=TEXTJOIN(", ", TRUE, IF($E$2:$E$3963=E77, $D$2:$D$3963, ""))

Then i used which removed the duplicates but now is glitchy... besides i don't understand how it ever worked.

=TEXTJOIN(", ", TRUE, UNIQUE(FILTERXML("<root><element>" & SUBSTITUTE(N63, ", ", "</element><element>") & "</element></root>", "//element")))

Would love to use power query instead of formulas but happy for any help at all :)

[#2]

@JonBastians 

It all depends upon the version of Excel.  Solutions I write for 365 are not remotely similar to anything that might have been done for traditional spreadsheets. 

= LET(
    distinctChild,     UNIQUE(child),
    associatedWorkers, MAP(distinctChild,
      LAMBDA(name,
        LET(
          worker,      FILTER(supportWorker, child=name),
          distinct,    UNIQUE(worker),
          TEXTJOIN(", ",,distinct)
        )
      )
    ),
    HSTACK(distinctChild, associatedWorkers)
  )

With named Lambda functions, this can be tidied up but, maybe, that is a step too far!

= LET(
    distinctChild,     UNIQUE(child),
    associatedWorkers, MAP(distinctChild, ListSupportλ),
    HSTACK(distinctChild, associatedWorkers)
  )

[#3]

@https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/214174
Thankyou so much for the neat solution! I downloaded your file and it runs on my computer. I will have to try it at work. Fingers crossed! I will let you know :)