Inverse of unpivot

[Anonymous]

I have the following data set of accounts and team members, no account will have more than 2/3 team members however I want it with the account id column to be unique. Preferably I would like this to be done in Power Query but if it can only be done in dax then that could work.

Account ID | Team Member

1                 | Alice

1                 | Bob

1                 | Chris

2                 | Dan

3                 | Emma

3                 | Frank

What I want to do is end up with 

Account ID | First User | Second User | Third User

1                 | Alice        | Bob               | Chris 

2                 | Dan         | null                | null

3                 | Emma     | Frank             | null

I would know how to go from what I want to what I have with unpivot however I have been unable to do the reverse with group or pivot.

Thank you for your help

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Here is one way using DAX calculated Table

From the Modelling Tab>>New Table

Table =
SUMMARIZE (
    Table1,
    Table1[Account ID ],
    "First User", CALCULATE (
        FIRSTNONBLANK ( Table1[ Team Member], 1 ),
        FILTER (
            VALUES ( Table1[ Team Member] ),
            RANKX (
                VALUES ( Table1[ Team Member] ),
                CALCULATE ( VALUES ( Table1[ Team Member] ) )
            )
                = 1
        )
    ),
    "Second User", CALCULATE (
        FIRSTNONBLANK ( Table1[ Team Member], 1 ),
        FILTER (
            VALUES ( Table1[ Team Member] ),
            RANKX (
                VALUES ( Table1[ Team Member] ),
                CALCULATE ( VALUES ( Table1[ Team Member] ) )
            )
                = 2
        )
    ),
    "Third User", CALCULATE (
        FIRSTNONBLANK ( Table1[ Team Member], 1 ),
        FILTER (
            VALUES ( Table1[ Team Member] ),
            RANKX (
                VALUES ( Table1[ Team Member] ),
                CALCULATE ( VALUES ( Table1[ Team Member] ) )
            )
                = 3
        )
    )
)

[Zubair_Muhammad]

@Anonymous

[Anonymous]

Thanks @Zubair_Muhammad that seems to be perfect.