Unpivoting in DAX

[newpbiuser01]

Hello,

I currently have 8 different tables that are related to one another and I'm pulling in a name from each of the tables to create a comma seperated list of reportees for each manager. Unfortunately, I can't do it in Power Query unless I merge the 8 tables one by one and given the size of the data and the number of tables (8) that's really messing up the performance! 

So instead I add the relationships between the tables and then generate the table 1 reportees column. What I'd like to do now is unpivot the Table 1 below to get each of the reportees for each manager (Table 2).

Table I

Manager	Reportees
Bob	Mike, Andy, Liz, Christine
Sally	Liz, Christine
Tom	Dave, Sally, Liz, Christine

I need to get this:

Manager	Reportee
Bob	Mike
Bob	Andy
Bob	Liz
Bob	Christine
Sally	Liz
Sally	Christine
Tom	Dave
Tom	Sally
Tom	Liz
Tom	Christine

Has anyone tried to do this and know how I could do it in DAX? 

[tamerj1 · Accepted solution]

Hi @newpbiuser01 

Please try

Table2 =
seleactcolumns (
    GENERATE (
        Table1,
        VAR String = Table1[Reportees]
        VAR Items =
            SUBSTITUTE ( String, ", ", "|" )
        VAR Length =
            PATHLENGTH ( Items )
        VAR T =
            GENERATESERIES ( 1, Length, 1 )
        RETURN
            SELECTCOLUMNS ( T, "Reportee", PATHITEM ( Items, [Value] ) )
    ),
    "Manager",
    [Manager],
    "Reportee",
    [Reportee]
)

[newpbiuser01]

Thank you!