Forum Discussion
Syntax to join values from multiple table cells into one Table cell
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!
5 Replies
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, 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 | zPerson | 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!
~SanHi,
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.
