Forum Discussion

PowerK's avatar
PowerK
Frequent Visitor
2 years ago
Solved

Need help with creating table

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...
Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    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.

vicky_'s avatar
vicky_
Super User
2 years ago

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's avatar
    PowerK
    Frequent Visitor
    2 years ago

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