Identifying contracts with a large over lap of line items

[Cthies1965]

Hello,

I have the following challenge:

I have a list of contract line items:

Contact ID	Line item ID	Description
10001	10	Item A
10001	20	Item B
10001	30	Item C
10002	10	Item D
10002	20	Item A
10003	10	Item A
10003	20	Item B
10003	30	Item D
10003	40	Item E
10004	10	Item F

I would like to identify for each Contract ID which other contract have an overlap of line item Description of more than 49%.

So the expected out come should be the following:

Base Contract ID	Comparison Contract ID	Percent Overlap
10001	10003	66.6
10002	10001	50.0
10002	10003	100.0
10003	10001	50.0
10003	10002	50.0

I hope that something like this is possible with M code and would appreciate any advice.

Thank You.

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source, 
        "Contact ID", 
        {{"items", each [Line item ID]}, {"count", Table.RowCount}}
    ), 
    others = Table.AddColumn(group, "others", each group),
    xp = Table.ExpandTableColumn(
        others, 
        "others", 
        {"Contact ID", "items"}, 
        {"Comparison Contract ID", "comp_items"}
    ),
    overlap = Table.AddColumn(
        xp, 
        "overlap", 
        (x) => List.Count(List.Intersect({x[items], x[comp_items]})) / x[count], 
        Percentage.Type
    )[[Contact ID], [Comparison Contract ID], [overlap]],
    filter = Table.SelectRows(overlap, each [Contact ID] <> [Comparison Contract ID] and [overlap] > .49)
in
    filter

[Cthies1965]

Fantastic, works like a charm. I think the key function here is the List.Intersect. Will have to study your solution a bit longer. Thanks a lot.