Cross-correlation between two groups

[gesposito]

Good morning, everyone,
suppose we have a list of people, their families and the work groups to which they belong.
Let us assume that people can only get to know each other through family or work contacts.
I would like to isolate 'knowledge groups' with all the people who can abstractly know each other on the basis of these memberships.
Two different 'knowledge groups' should never include members of the same family or work organisation.
How is it possible to compose these groups using powerquery? I hope someone can give me some advice and I thank you in advance.
I enclose sample tables of the available data and the hoped-for result (blank households or work groups are unknown or absent).

[dufoq3]

Hi @gesposito,

are you sure that expected result you provided is 100% correct? I would say that Person ID 5 and 6 should be included to Knowledge group ID A, but maybe I'm wrong. Could you describe more in detail with examples in description please?

[gesposito]

Hi @dufoq3 , you are absolutely right, people 5 and 6 must be part of the knowledge A group.

Let me try to explain the logic of my request better:
A1) people 3, 7 and 11 are part of the same family (1) so they will automatically be part of the same knowledge group (A)
A2) person 7 belongs to work group 2, person 11 to work group 1. Therefore the first knowledge group (A) will be extended to all those who belong to work groups 2 and 1, i.e. persons 5, 6, 14 and 17.
A3) These persons do not belong to any family, so the chain of possible knowledge is broken and group (A) is thus closed.

B1) we form a new group: persons 2 and 9 are part of the same family (3) so they will automatically be part of the same knowledge group (B)
B2) person 2 is part of work group 4 so group B extends to person 1 who is part of work group 4
B3) person 1 is part of family 4 which has two other members 12 and 13 who are added to group B
B4) Since persons 12 and 13 are not part of any working group, the chain is broken and the group is thus closed.

I thus obtain 2 groups (A and B).
People in one group could (by assumption) potentially know each other because they have families or work groups in common.
Between the two groups, however, there is no possibility of getting to know each other because neither family nor work group unites them in any way.
For the solution to the problem I would imagine a table/matrix where the rows are the families, the columns are the work groups and in the cells are the persons......but I do not know how to analyse such a table to create groups

[dufoq3 · Accepted solution]

Hi, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BDcAgCAXQXTh7KFC1nYW4/xoV+Bp7MPkvgGBGTIXueKMYyUy65cnrAW9auWaWQDvRMZJ6sqSBF38H+Dq+Y8YUJwU3pfQnnIHOGqvWBm5gqu/W8QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person ID" = _t, #"Family ID" = _t, #"Work Group ID" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Person ID", Int64.Type}, {"Family ID", Int64.Type}, {"Work Group ID", Int64.Type}}),
    GroupedFamily = Table.Group(ChangedType, {"Family ID"}, {{"Person IDs", each [Person ID], type list}}),
    GroupedWork = Table.Group(ChangedType, {"Work Group ID"}, {{"Person IDs", each [Person ID], type list}}),
    StepBack = ChangedType,
    FilteredRows = Table.SelectRows(StepBack, each ([Family ID] <> null) and ([Work Group ID] <> null)),
    MergedFamilyGroup = Table.NestedJoin(FilteredRows, {"Family ID"}, GroupedFamily, {"Family ID"}, "GroupedFamily", JoinKind.LeftOuter),
    ExpandedGroupedFamily = Table.ExpandTableColumn(MergedFamilyGroup, "GroupedFamily", {"Person IDs"}, {"Family Person IDs"}),
    MergedGroupedWork = Table.NestedJoin(ExpandedGroupedFamily, {"Work Group ID"}, GroupedWork, {"Work Group ID"}, "GroupedWork", JoinKind.LeftOuter),
    ExpandedGroupedWork = Table.ExpandTableColumn(MergedGroupedWork, "GroupedWork", {"Person IDs"}, {"Work Person IDs"}),
    RemovedColumns = Table.RemoveColumns(ExpandedGroupedWork,{"Person ID"}),
    Ad_PersonIDs = Table.AddColumn(RemovedColumns, "Person IDs", each List.Distinct(List.Combine({[Family Person IDs], [Work Person IDs]}))),
    GroupedWork2 = Table.Group(Ad_PersonIDs, {"Work Group ID"},
        { {"Family IDs", each Text.Combine(List.Transform([Family ID], Text.From), ";"), type text},
          {"Person IDs", each List.Combine([Person IDs]), type list} }),
    GroupedFamily2 = Table.Group(GroupedWork2, {"Family IDs"},
        { {"Work Group IDs", each Text.Combine(List.Transform([Work Group ID], Text.From), ";"), type text},
          {"Person IDs", each Text.Combine(List.Transform(List.Distinct(List.Combine([Person IDs])), Text.From), ";"), type text} }),
    Ad_KnowledgeGroupID = Table.AddIndexColumn(GroupedFamily2, "Knowledge Group ID", 1, 1, Int64.Type),
    KnowledgeGroupIdToLetter = Table.TransformColumns(Ad_KnowledgeGroupID, {{"Knowledge Group ID", each Character.FromNumber(_ + 64), type text}}),
    ReorderedColumns = Table.ReorderColumns(KnowledgeGroupIdToLetter,{"Knowledge Group ID", "Family IDs", "Work Group IDs", "Person IDs"})
in
    ReorderedColumns

[gesposito]

This seems like a brilliant solution. Thank you very much and I hope I can understand all the steps you have taken so that I can acquire them for the future

[dufoq3]

You're welcome.

[gesposito]

I try to represent the groupings by means of a table. I have omitted people who are not part of any grouping. Thanks again to those who are willing to help me.