Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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]
)
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]
)
Thank you!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |