March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
22 | |
20 | |
18 |