So caveat emptor on using experimental features. However turning on the "Record Scope" feature does do the trick. It is not straight forward though - despite this feature "allowing table operations", the fact remains (per my previous post) that the relationship doesn't manifest as a column we can reference, so we can't just simply ungroup it.
I am not saying this is the perfect answer, but it works.
This entire solution is based on Concat() being the only function I could get to actually reference the intersect table property of the NameTable datasource (there may be others).
Note to produce the data in the intersect table, you only need 3 steps. The 2 additional steps are only to group by category, per your original requirement. Here's the break down of the 5 steps, you could nest these but it doesn't make it easy to understand:
//Collect the related data as a nested delimited list
ClearCollect(col_Result_A, ForAll(NameTables As T1, {Name: T1.Name, xExpertise: Concat(T1.ExpertiseTables, Category & ";" & Expertise, "|")}));
Produces:
//Initiliase the next collection, we will use it as a Collect-only in the ForAll
ClearCollect(col_Result_B, Blank());
//Split the outer delimited values onto their own rows, collect in the inner collection col_Result_B, col_temp is just a dummy
ClearCollect(col_temp, ForAll(col_Result_A, ForAll(Split(xExpertise, "|"), Collect(col_Result_B, {Name:Name, xSplit: Value}))));
Produces (the Value column comes from initialising with Blank()):
//Split the category/expertise pairs into their own columns
ClearCollect(col_Result_C, ForAll(col_Result_B, {Name: Name, Category: First(Split(xSplit, ";")).Value, Software: Last(Split(xSplit, ";")).Value}));
Produces:
//Group expertise by name/category
ClearCollect(col_Result_D, GroupBy(col_Result_C, "Name", "Category", "Expertise"));
Produces:
//Create pipe delimited list of expertise, now by the groupings
ClearCollect(col_Result_Final, ForAll(col_Result_D, {Name: Name, Category: Category, Expertise: Concat(Expertise, Software, "|")}) );
Produces:
Use this collection as the Items property of a table, and you have your original requirement.
