Need help with creating table

[PowerK]

Hi!

I have created an example. I have this kind of data in table:

I need to  see who has competed together with who in the same team and how many times. For example if I choose Alma from slicer, then I see that she has competed in the same team with Ketter (twice), Maria (once), Lisa (once).

So I think that I need this kind of table to count how many times they have competed together (example is done manually). Is there any way to do this kind of table automatically?

Thanks in advance!

[vicky_ · Accepted solution]

Here's one possible solution:

I first go into PowerQuery and merge the table with itself.

And then I expand the table, but I only need the Competition and Name columns: 

Lastly, I have a query to remove the rows where it's the same person playing (i.e in row 1 - name1 is playing with name1)

The query is something like: 

= Table.SelectRows(#"Expanded Changed Type", each ([competition] = [competition.1] and [name] <> [name.1]))

I hope this helps.

[PowerK]

Thanks! I had to modify a bit to get it exactly what I wanted, but this helped a lot!