Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |