Syntax to join values from multiple table cells into one Table cell

[sanpanico]

Good day!

I am attempting to join values from multiple table cells into one Table cell.  Simple right?!
Using =Join(Fields!XXX.Value, ", ") causes "#Error" to show up in the table cell, I assume because Fields!XXX.Value is not actually a multi-value field, it's a single value per the main identifier. 

I have gotten it to work in a separate text field, however the Table field behaves differently.  I've attempted to implement Report Builder's functions like Aggregate, Lookup with no success.   Also, I am NOT able to implement custom SQL, and as such am relying on the native expression builder.
Any and all suggestions welcome!

[parry2k · Accepted solution]

@sanpanico RDL file is attached. I created the sample from your data, and here is the output:

[sanpanico]

parry2k, many thanks!
As mentioned, I also tried Lookup with minor success.  My issue remains that for each occurence of a name (it's actually an ID field), the joins are returned in each cell for as many rows as an ID occurs.  I am going to test the creation of a separate Data Set, and remove the IDs from my main query/Data Set.
I will update this post if it works, but I have accepted your solution as well
Thanks again!
~San

[parry2k]

@sanpanico sorry not sure what you are trying to ? Are you trying to join columns of a table, or rows?

[sanpanico]

parry2k, thank you, and apologies. 

I am attempting to join rows of separate cells within the same table column.

Here is an example of the before and after;

Person | StrID
--------------
Jim    | a
Jim    | b
Jim    | c
Jim    | d
Mary   | h
Mary   | k
Sue    | l
Sue    | m
Sue    | p
Sue    | z

Person | StrID's
-------------------
Jim    | a, b, c, d
Mary   | h, k
Sue    | l, m, p, z

There are many examples of how to implement this using SQL, but again, I am NOT able to implement custom SQL, and as such am relying on the Report Builder's expression builder.
Many thanks!
~San

[Ashish_Mathur]

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Person"}, {{"Count", each Text.Combine(_[StrID],", ")}})
in
    #"Grouped Rows"

Hope this helps.