let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Roles = List.Buffer(List.Distinct(Source[role])), //extracts from previous step (which is a table) the column "role" and gets the unique values. Buffer it to keep the list in memory. This makes it dynamic.
GroupBranch = Table.Group(Source, {"branch"}, {{"All", each _, type table [branch=text, role=text]}}), //Aggregate using all row to create a segmented table
GroupPivAll = Table.AddColumn(GroupBranch, "GroupCount", each let
Grp = Table.Group([All], {"role"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Piv = Table.Pivot(Grp, Roles, "role", "Count", List.Sum)
in
Piv), // is a custom column holding a "mini query" It groups the sub table or segment, counts the rows and pivots the roles (the buffered list) When you drill down on a table object, you can continue using the UI. Then copy these lines from the advanced editor and paste it in an custom column. Do not fortget to delete everything from the drill down on.
ExpandGroupPivAll = Table.ExpandTableColumn(GroupPivAll, "GroupCount", Roles, Roles), // Expand this tabble and use the columns that match a "role", again using the buffered list
ExpandAll = Table.ExpandTableColumn(ExpandGroupPivAll, "All", {"role"}, {"role"}), // Expand the segmented table, only need to recover the original rows by expanding the original role column
Replace_null = Table.ReplaceValue(ExpandAll,null,0,Replacer.ReplaceValue,Roles),// Replace null values with zero, so we can create the code. This is needed for all the columns with a count in it. You guesses it, the buffered list once more.
AddColCode = Table.AddColumn(Replace_null, "Code", each List.Accumulate(
Record.FieldValues( Record.SelectFields(_,Roles)), "", (state, current) => state & Text.From(current)), type text) // "_" creates a record for each row of the table. But we only need the values from the "roles" columns. Se we Select the fields (Record.SelectFields) by using the buffered list a last time. Yet we need the values (record.FieldValues). This gives us a list. To concatenate each element we can use List.Accumulate. The seed values is "", an empty string. state is the previous result and we concatenate the current value. Since these values are numbers, we need to transform them to text first. So for the first record it actually creates these intermediate values: "", "2", "21", "211", "2100". The last value is finally retained.
in
AddColCode