Let’s try something different. I will share a data analytics challenge here. Post your solutions in the comments.
Our first challenge involves Employee Data Analysis. You can score maximum of 35 points.
Imagine you have employee data in this “staff” table (in Excel / Power BI / SQL / Python or whatever tool you fancy)
Download sample data file (it has all the 28 employees) or use the below CSV.
Emp ID Name Reports to
TO-0002 Jim Halpert TO-0007
TO-0004 Pam Beesly TO-0015
TO-0007 Michael Scott TO-0067
TO-0009 Erin Hannon TO-0004
TO-0010 Jan Levinson TO-0067
TO-0013 Karen Filippelli TO-0076
TO-0015 Dwight Schrute TO-0007
TO-0018 Andy Bernard TO-0002
TO-0021 Cathy Simms TO-0002
TO-0024 Kevin Malone TO-0035
TO-0028 Creed Bratton TO-0007
TO-0030 Toby Flenderson TO-0067
TO-0031 Kelly Kapoor TO-0028
TO-0033 Robert California TO-0067
TO-0035 Angela Martin TO-0007
TO-0039 Stanley Hudson TO-0007
TO-0043 Ryan Howard TO-0031
TO-0046 Oscar Martinez TO-0035
TO-0050 Meredith Palmer TO-0028
TO-0051 Gabe Lewis TO-0072
TO-0055 Phyllis Vance TO-0030
TO-0059 Roy Anderson TO-0004
TO-0063 Nellie Bertram TO-0030
TO-0066 Darryl Philbin TO-0039
TO-0067 David Wallace TO-0072
TO-0068 Pete Miller TO-0015
TO-0072 Jo Bennet
TO-0076 Todd Parker TO-0002
1. How many people are directly reporting to my boss? (5pts)
The first question is simple. For a given employee ID (say TO-0021, Cathy Simms), how many people are directly reporting to their boss (TO-0002, Jim Halpert)? The answer should be 3.
Write the necessary Excel formula / DAX / Power Query / SQL or Python code to find the answer.
2. Who is my boss’s boss? (10pts)
We are going to level up. For a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss. The answer should be TO-0007, Michael Scott.
3. What is my reporting chain? (Score=20pts)
For a given employee (say TO-0021, Cathy Simms), print their entire reporting chain, delimited by the symbol ->.
Expected Answer is:
Cathy->Jim->Michael->David->Jo
Note: You may assume a maximum depth of 7 nodes if that helps.
Download Sample File
Grab the sample data file (Excel format) here. Use it to solve the problems. Alternatively, I have pasted the data above. Copy it and paste it in Python or SQL.
Post your answers below.
Leave a comment with your solutions / approach. All the best
Want more?
Check out my Excel Homework Tag page for more challenges and problems.
57 Responses to “Who is my boss’s boss? [Data Analytics Challenge – 001]”
I ripped a recursive lambda solution from https://exceldashboardschool.com/lambda-function/recursive-examples/ to get the boss chain.
Named lambda formula "CATEGORYCHAIN":
=LAMBDA(prod,products,categories,
LET(
parent, XLOOKUP(prod, products, categories, ""),
IF(parent="", TAKE(TEXTSPLIT(XLOOKUP(prod,[Emp ID],[Name])," "),1,1), TAKE(TEXTSPLIT(XLOOKUP(prod,[Emp ID],[Name])," "),1,1) & "->" & CATEGORYCHAIN(parent, products, categories))
)
)
prod is the employee code, products is the [Emp ID] column, categories is the [Reports to] column
1. =COUNTIF([Reports to],[@[Reports to]])
2. =XLOOKUP([@[Reports to]],[Emp ID],[Reports to],"",0)
3. Got this far and then got dizzy trying to "recurse" up the chain:
=LEFT([@Name],FIND(" ",[@Name])-1)&"->"&LEFT(XLOOKUP([@[Reports to]],[Emp ID],[Name],"",0),FIND(" ",XLOOKUP([@[Reports to]],[Emp ID],[Name],"",0))-1)&"->"&LEFT(XLOOKUP([@[Boss''Boss]],[Emp ID],[Name],"",0),FIND(" ",XLOOKUP([@[Boss''Boss]],[Emp ID],[Name],"",0)))
1)
=COUNTIF([Reports to],[@[Reports to]])
2)
=CONCAT(XLOOKUP([@[Reports to]],[Emp ID],[Reports to]," "), ", ",
XLOOKUP(XLOOKUP([@[Reports to]],[Emp ID],[Reports to],"None"),[Emp ID],[Name]," "))
3)
=CONCAT(TEXTBEFORE([@Name]," "),"->",
IFERROR(TEXTBEFORE(XLOOKUP([@[Reports to]],[Emp ID],[Name],"")," "),""),"->",
IFERROR(TEXTBEFORE(XLOOKUP(XLOOKUP([@[Reports to]],[Emp ID],[Reports to],""),[Emp ID],[Name])," "),""),"->",
IFERROR(TEXTBEFORE(XLOOKUP(XLOOKUP(XLOOKUP((XLOOKUP([@[Reports to]],[Emp ID],[Reports to],"")),[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Name],"")," "),""))
Hey Sir, I only use Excel.
Question 1:
a) =COUNTIF(C2:C16, VLOOKUP("TO-0021", A2:C16, 3, FALSE))
or simply
b) =COUNT.IF(staff[Reports to];RECHERCHEV("TO-0021";staff[[Emp ID]:[Reports to]];3;FAUX))
Question 2:
=RECHERCHEV(RECHERCHEV(M14;staff[[Emp ID]:[Reports to]];3;FAUX);staff[[Emp ID]:[Reports to]];3;FAUX)
To find another thing I can just change the col_index
Question 3:
I have not been able to do that in Excel.
But I tried with this ?
=RECHERCHEV(M14;staff[[Emp ID]:[Reports to]];2;FAUX)&"->"&RECHERCHEV(N14;staff[[Emp ID]:[Reports to]];2;FAUX)&"->"&RECHERCHEV(O14;staff[[Emp ID]:[Reports to]];2;FAUX)
The last one got me!
Hi Chandoo
Had some fun doing the problems. I retired in 2019 and doing this was enjoyable. It could probably be done more efficiently with lambda functions.
Thanks
Virendra
SOLUTION: First I added two names than there was not in the table: TO-0067 i named "Top Exec." and TO-0076 i named "Karen Filippelli's Boss"
FIRST QUESTION:
For a given employee ID (say TO-0021, Cathy Simms), how many people are directly reporting to their boss (TO-0002, Jim Halpert)?
ANSWER:
=COUNTA(CHOOSECOLS(FILTER(Staff,Staff[Boss ID]=XLOOKUP(B21,Staff[Name],Staff[Boss ID])),3))
SECOND QUESTION:
For a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss. The answer should be TO-0007, Michael Scott.
ANSWER:
Their boss’s boss is two levels up: 2
Using Lambda formula: Assigned to function name: NthManagerName
=LAMBDA(empID, n,
LET(
ids, Staff[Emp ID],
boss, Staff[Boss ID],
names, Staff[Name],
upID, REDUCE(empID, SEQUENCE(n), LAMBDA(acc, _, XLOOKUP(acc, ids, boss, ""))),
XLOOKUP(upID, ids, names, "")
)
)
Then Use
=NthManagerName(B25,B27)
THIRD QUESTION:
For a given employee (say TO-0021, Cathy Simms), print their entire reporting chain, delimited by the symbol ->. Expected Answer is: Cathy->Jim->Michael->David->Jo
ANSWER:
Using the “NthManagerName” Lambda function over an sequence 1 to 20 and then TEXTJOIN the names:
=TEXTJOIN(" -> " TRUE,Bosses)
My final solution for #3 (took awhile but realized I needed to take a different approach):
=LET(
ids, TRIM([Emp ID] & ""),
mgrs, TRIM([Reports to] & ""),
nm, [Name],
firsts, IFERROR(LEFT(nm, FIND(" ", nm & " ") - 1), nm),
me, TRIM([@[Emp ID]] & ""),
chainIDs,
HSTACK(
me,
SCAN(
me,
SEQUENCE(5),
LAMBDA(prev,_,
IF(prev="","", IFERROR(XLOOKUP(prev, ids, mgrs, ""), ""))
)
)
),
chainNames, IFERROR(XLOOKUP(chainIDs, ids, firsts, ""), ""),
TEXTJOIN("->", TRUE, chainNames)
)
I used the sledgehammer method.
=staff[@[Emp ID]] in a cell
=XLOOKUP(P4,$B:$B,$D:$D) in a neighboring cell
Repeat until you run into #N/A
Then
=TEXTBEFORE(XLOOKUP(P4,$B:$B,$C:$C)," ") In a cell and copied until you hit #N/A
Then
=TEXTJOIN("->",TRUE,IF(ISNA(K4:O4),"",K4:O4)) in the Chain cell
Sledge hammer ?
1. I did a pivot table
2. =XLOOKUP([@[Reports to]],A:A,B:B)
3. =IFERROR(TEXTBEFORE([@[Mgr''sName]]," ")&"->"&TEXTBEFORE([@[SrMgr''sName]]," ")&"->"&TEXTBEFORE([@3rdLevel]," ")&"->"&TEXTBEFORE([@4thLevel]," "),"")
1. =COUNTIFS([Boss ID],[@[Boss ID]])
2. =LET(MyVal,XLOOKUP([@[Boss ID]],[Emp ID],[Boss ID],""),IF(OR(MyVal=0,MyVal=""),"Nobody!",MyVal))
3. Based on 8 possible levels WITH Helper Columns …
=TEXTJOIN(" -> ",TRUE,XLOOKUP(LET(MyRow, HSTACK([@[Emp ID]],Staff[@[Boss ID]:[Reports to L8]]),FILTER(MyRow,NOT(ISNUMBER(MyRow))*(MyRow""),"No data!")),[Emp ID],TEXTBEFORE([Name]," "),""))
NB: I left the [# Reports to my boss] column at its place, what annoyed me a little 😉
VBA must be much much easier ! Don't know Python (yet) and long to discover/learn recursive Lambda 😉
Here are the SQL queries
--select * from staff;
--1. How many people are directly reporting to my boss?
select * from staff
where [Reports to]=(select [Reports to]
from staff
where [Emp ID]='TO-0021');
--2. Who is my boss’s boss?
select * from staff where [Emp ID]=(
select [Reports to] from staff where [Emp ID]=(
select [Reports to]
from staff
where [Emp ID]='TO-0021'));
--3. What is my reporting chain?
--DECLARE @EmployeeID varchar = 'TO-0021';
with emp_tree as(
select [Reports to] as ManagerID, [Emp ID] as EmpID,Name as EmpName,1 as LEVEL
from staff where [Emp ID] = 'TO-0002'
union all
select s1.[Reports to] as ManagerID,s1.[Emp ID] as EmpID,Name as EmpName,(e.LEVEL+1) as LEVEL
from staff s1
join emp_tree e on e.ManagerID = s1.[Emp ID]
)
select STRING_AGG(left(EmpName,CHARINDEX(' ',EmpName)),'->') as reporting_chain from emp_tree;
1) =COUNTIF([Reports to],[@[Reports to]])
2) =XLOOKUP([@[Reports to]],[Emp ID],[Reports to])
3) got a bit lost while trying to write a lambda function
-- 1st submission
select e1.EmpID,e1.Name,e1.BossID as reports from Project.dbo.Employees as e inner join Project.dbo.Employees as e1
on e.EmpID = e1.BossID
where e.EmpID = 'TO-0002'
-- 2nd mission
DECLARE @EmpID VARCHAR(10) = 'TO-0021';
select fact.EmpID,fact.Name,boss.Name as reportingmanager, bb.Name as Headmanager from Project.dbo.Employees as fact
left join Project.dbo.Employees as boss on fact.BossID = boss.EmpID
left join Project.dbo.Employees as bb on boss.BossID = bb.EmpID
where fact.EmpID = @EmpID
#1 & 2 are trivial, but here is my solution for 3.
Thought about doing recursion, but settled on using Scan instead, set up that it can work even if the whole table is a line of reporting.
=LET(
output,SCAN([@[Emp ID]],SEQUENCE(ROWS([Emp ID])),LAMBDA(id,boss,XLOOKUP(id,[Emp ID],[Reports to],0))),
cleaned,FILTER(output,output0),
bosses,VSTACK([@Name],XLOOKUP(cleaned,[Emp ID],[Name])),
TEXTJOIN("->",,TEXTBEFORE(bosses," ")))
Hi Danny
Can I ask two dumb questions about your elegant solution (I'm only just starting out learning about recursive-type techniques):
1. Firstly I'm getting a #Name? error, but I literally copied your code (and only had to update the table names with underscores for the formula to be accepted)
2. I almost understand the formula (if I did, I would be able to solve #1!), but can you explain where output0 comes from please? Is this just the first array element from SCAN?
Many thanks in advance, Julian
Answers in PQ
Q1
let
// Load source data
Source = Data,
// Join employees to their direct bosses
WithBoss = Table.NestedJoin(Source, {"Reports to"}, Source, {"Emp ID"}, "Boss_Data", JoinKind.LeftOuter),
// Select employee name and boss details only
EmployeeBoss = Table.SelectColumns(WithBoss, {"Name", "Boss_Data"}),
// Expand boss details to get boss ID and name
ExpandedBoss = Table.ExpandTableColumn(EmployeeBoss, "Boss_Data", {"Emp ID", "Name"}, {"Boss_ID", "Boss_Name"}),
// Group by boss to get employee count and list of employees
GroupedByBoss = Table.Group(
ExpandedBoss,
{"Boss_ID", "Boss_Name"},
{
{"Employee_Count", each Table.RowCount(_), Int64.Type},
{"Employee_List", each Text.Combine([Name], ", "), type text}
}
),
// Sort by boss ID ascending
SortedResult = Table.Sort(GroupedByBoss, {{"Boss_ID", Order.Ascending}})
in
SortedResult
Q2)
let
// Load source data
Source = Data,
// Join to get direct boss details
WithBoss = Table.NestedJoin(
Source, {"Reports to"},
Source, {"Emp ID"},
"Boss_Data", JoinKind.LeftOuter
),
ExpandedBoss = Table.ExpandTableColumn(
WithBoss,
"Boss_Data",
{"Emp ID", "Name", "Reports to"},
{"Boss_ID", "Boss_Name", "Boss_ReportsTo"}
),
// Join to get boss's boss details
WithBossBoss = Table.NestedJoin(
ExpandedBoss, {"Boss_ReportsTo"},
Source, {"Emp ID"},
"BossBoss_Data", JoinKind.LeftOuter
),
ExpandedBossBoss = Table.ExpandTableColumn(
WithBossBoss,
"BossBoss_Data",
{"Emp ID", "Name"},
{"BossBoss_ID", "BossBoss_Name"}
),
// Select relevant columns
SelectedColumns = Table.SelectColumns(
ExpandedBossBoss,
{"Emp ID", "Name", "Boss_ID", "Boss_Name", "BossBoss_ID", "BossBoss_Name"}
),
// Sort by employee ID
Result = Table.Sort(SelectedColumns, {{"Emp ID", Order.Ascending}})
in
Result
Q3
let
// Prepare data with Emp ID and ReportsTo as text for consistent matching
EmployeeData = Table.TransformColumns(
Table.SelectColumns(Data, {"Emp ID", "Name", "Reports to"}),
{{"Emp ID", Text.From, type text}, {"Reports to", Text.From, type text}}
),
// Retrieve employee record by Emp ID
GetRecord = (id as text) =>
let row = Table.SelectRows(EmployeeData, each [Emp ID] = id) in
if Table.IsEmpty(row) then null else row{0},
// Iteratively build reporting chain using List.Accumulate with early exit
BuildChain = (startID as text) =>
let
MaxDepth = 50,
AccFunc = (acc, _) =>
let
chain = acc{0},
currentID = acc{1},
visited = acc{2},
stop = currentID = null or currentID = "" or List.Contains(visited, currentID),
rec = if stop then null else GetRecord(currentID),
name = if rec = null then null else rec[Name],
nextID = if rec = null then null else rec[Reports to],
newChain = if stop then chain else List.Combine({chain, {name}}),
newVisited = if stop then visited else List.Combine({visited, {currentID}})
in
{newChain, nextID, newVisited},
result = List.Accumulate(List.Repeat({0}, MaxDepth), {{}, startID, {}}, AccFunc)
in
result{0},
// Add Reporting Chain column, join names with " -> "
AddChainCol = Table.AddColumn(EmployeeData, "Reporting Chain", each Text.Combine(List.RemoveNulls(BuildChain([Emp ID])), " -> "), type text),
// Sort by Emp ID ascending
Sorted = Table.Sort(AddChainCol, {{"Emp ID", Order.Ascending}})
in
Sorted
Answer Python
Q1)
# Source Data
df = xl("Data[#All]", headers=True)
# Merge with itself to get boss details
merged = df.merge(
df[['Emp ID', 'Name']],
left_on='Reports to',
right_on='Emp ID',
how='left',
suffixes=('', '_Boss')
)
# Rename for clarity
merged.rename(columns={'Emp ID_Boss': 'Boss_ID', 'Name_Boss': 'Boss_Name'}, inplace=True)
# Group by Boss and aggregate
grouped = merged.groupby(['Boss_ID', 'Boss_Name']).agg(
Employee_Count=('Name', 'count'),
Employee_List=('Name', lambda x: ', '.join(x))
).reset_index()
Q2)
# Source Data
df = xl("Data[#All]", headers=True)
# Merge to get direct boss details
merged_with_boss = df.merge(
df[['Emp ID', 'Name', 'Reports to']],
left_on='Reports to',
right_on='Emp ID',
how='left',
suffixes=('', '_Boss')
)
# Rename for clarity
merged_with_boss.rename(columns={
'Emp ID_Boss': 'Boss_ID',
'Name_Boss': 'Boss_Name',
'Reports to_Boss': 'Boss_ReportsTo'
}, inplace=True)
# Merge again to get the boss's boss details
merged_with_bossboss = merged_with_boss.merge(
df[['Emp ID', 'Name']],
left_on='Boss_ReportsTo',
right_on='Emp ID',
how='left',
suffixes=('', '_BossBoss')
)
# Rename for clarity
merged_with_bossboss.rename(columns={
'Emp ID_BossBoss': 'BossBoss_ID',
'Name_BossBoss': 'BossBoss_Name'
}, inplace=True)
# Select final useful columns
final_result = merged_with_bossboss[[
'Emp ID', 'Name', 'Boss_ID', 'Boss_Name', 'BossBoss_ID', 'BossBoss_Name'
]]
# Replace NaN/None with empty strings
final_result = final_result.fillna('')
final_result
Q3)
# Load source data
df = xl("Data[#All]", headers=True)
# Drop unnecessary columns safely
cols_to_drop = ['# Reports to my boss', "Boss'Boss", 'Chain']
df = df.drop(columns=cols_to_drop, errors='ignore')
# Ensure Emp ID and Reports to are strings; replace 'None' with blank
df['Emp ID'] = df['Emp ID'].astype(str)
df['Reports to'] = df['Reports to'].astype(str).replace('None', '')
# Create lookup dictionary for fast access
emp_dict = df.set_index('Emp ID')[['Name', 'Reports to']].to_dict(orient='index')
def build_reporting_chain(emp_id, max_depth=50):
chain = []
visited = set()
current = emp_id
for _ in range(max_depth):
if not current or current in visited:
break
visited.add(current)
rec = emp_dict.get(current)
if rec is None:
break
chain.append(rec['Name'])
current = rec['Reports to']
return " -> ".join(chain)
# Apply the function to build reporting chains
df['Reporting Chain'] = df['Emp ID'].apply(build_reporting_chain)
# Sort dataframe by Emp ID ascending
df = df.sort_values('Emp ID').reset_index(drop=True)
1)
=COUNTIF(staff[Reports to],staff[@[Reports to]])
2)
=XLOOKUP(staff[@[Reports to]],staff[Emp ID],staff[Reports to],"",0)
3)
=IFERROR(TEXTBEFORE(staff[@Name]," ") & " ?? " & LET(
Chain1,XLOOKUP(staff[@[Reports to]],staff[Emp ID],staff[[Emp ID]:[Reports to]],"",0),
Chain2,XLOOKUP(INDEX(Chain1,,3),staff[Emp ID],staff[[Emp ID]:[Reports to]],"",0),
Chain3,XLOOKUP(INDEX(Chain2,,3),staff[Emp ID],staff[[Emp ID]:[Reports to]],"",0),
Chain4,XLOOKUP(INDEX(Chain3,,3),staff[Emp ID],staff[[Emp ID]:[Reports to]],"",0),
Chain5,XLOOKUP(INDEX(Chain4,,3),staff[Emp ID],staff[[Emp ID]:[Reports to]],"",0),
Chain6,XLOOKUP(INDEX(Chain5,,3),staff[Emp ID],staff[[Emp ID]:[Reports to]],"",0),
TEXTBEFORE(INDEX(TEXTBEFORE(Chain1," ") & " ?? " &
IFERROR(TEXTBEFORE(Chain2," ") & " ?? ","") &
IFERROR(TEXTBEFORE(Chain3," ") & " ?? ","") &
IFERROR(TEXTBEFORE(Chain4," ") & " ?? ","") &
IFERROR(TEXTBEFORE(Chain5," ") & " ?? ","") &
IFERROR(TEXTBEFORE(Chain6," ") & " ?? ",""),,2)," ?? ",-1)),"")
My answer is by Query. I am from Slovakia and my excell is set up in slovak language (I love Power Query becouse I cannot use any program language such as Pyhon, DAX...whatever, just a little bit and with query I solved so many tasks).
let
Zdroj = staff,
#"Odstránené ostatné st?pce" = Table.SelectColumns(Zdroj,{"Emp ID", "Name", "Reports to"}),
#"Zlú?ené dotazy" = Table.NestedJoin(#"Odstránené ostatné st?pce", {"Reports to"}, group, {"Reports to"}, "boss_1", JoinKind.LeftOuter),
#"Rozbalené boss_1" = Table.ExpandTableColumn(#"Zlú?ené dotazy", "boss_1", {"# of reports to my boss"}, {"# of reports to my boss"}),
#"Zlú?ené dotazy1" = Table.NestedJoin(#"Rozbalené boss_1", {"Reports to"}, ID_list, {"Emp ID"}, "bosses_boss", JoinKind.LeftOuter),
#"Rozbalené bosses_boss" = Table.ExpandTableColumn(#"Zlú?ené dotazy1", "bosses_boss", {"Emp ID", "Name", "Reports to"}, {"Emp ID.1", "Name.1", "Reports to.1"}),
#"Premenované st?pce" = Table.RenameColumns(#"Rozbalené bosses_boss",{{"Reports to.1", "bosses_boss"}}),
#"Zlú?ené dotazy2" = Table.NestedJoin(#"Premenované st?pce", {"bosses_boss"}, ID_list, {"Emp ID"}, "bosses_boss.1", JoinKind.LeftOuter),
#"Rozbalené bosses_boss.1" = Table.ExpandTableColumn(#"Zlú?ené dotazy2", "bosses_boss.1", {"Name", "Reports to"}, {"Name.2", "Reports to.1"}),
#"Odstránené st?pce" = Table.RemoveColumns(#"Rozbalené bosses_boss.1",{"Emp ID.1"}),
#"Extrahoval sa text pred odde?ova?om" = Table.TransformColumns(#"Odstránené st?pce", {{"Name.1", each Text.BeforeDelimiter(_, " "), type text}}),
#"Zlú?ené dotazy3" = Table.NestedJoin(#"Extrahoval sa text pred odde?ova?om", {"Reports to.1"}, ID_list, {"Emp ID"}, "bosses_boss.1", JoinKind.LeftOuter),
#"Rozbalené bosses_boss.2" = Table.ExpandTableColumn(#"Zlú?ené dotazy3", "bosses_boss.1", {"Name", "Reports to"}, {"bosses_boss.1.Name", "bosses_boss.1.Reports to"}),
#"Premenované st?pce1" = Table.RenameColumns(#"Rozbalené bosses_boss.2",{{"Name.1", "chain_2"}, {"Name.2", "chain_3"}}),
#"Extrahoval sa text pred odde?ova?om1" = Table.TransformColumns(#"Premenované st?pce1", {{"chain_3", each Text.BeforeDelimiter(_, " "), type text}}),
#"Premenované st?pce2" = Table.RenameColumns(#"Extrahoval sa text pred odde?ova?om1",{{"bosses_boss.1.Name", "chain_4"}}),
#"Extrahoval sa text pred odde?ova?om2" = Table.TransformColumns(#"Premenované st?pce2", {{"chain_4", each Text.BeforeDelimiter(_, " "), type text}}),
#"Zlú?ené dotazy4" = Table.NestedJoin(#"Extrahoval sa text pred odde?ova?om2", {"bosses_boss.1.Reports to"}, ID_list, {"Emp ID"}, "bosses_boss.1", JoinKind.LeftOuter),
#"Rozbalené bosses_boss.3" = Table.ExpandTableColumn(#"Zlú?ené dotazy4", "bosses_boss.1", {"Name", "Reports to"}, {"bosses_boss.1.Name", "bosses_boss.1.Reports to.1"}),
#"Premenované st?pce3" = Table.RenameColumns(#"Rozbalené bosses_boss.3",{{"bosses_boss.1.Name", "chain_5"}}),
#"Zlú?ené dotazy5" = Table.NestedJoin(#"Premenované st?pce3", {"bosses_boss.1.Reports to.1"}, ID_list, {"Emp ID"}, "bosses_boss.1", JoinKind.LeftOuter),
#"Rozbalené bosses_boss.4" = Table.ExpandTableColumn(#"Zlú?ené dotazy5", "bosses_boss.1", {"Name", "Reports to"}, {"bosses_boss.1.Name", "bosses_boss.1.Reports to.2"}),
#"Premenované st?pce4" = Table.RenameColumns(#"Rozbalené bosses_boss.4",{{"bosses_boss.1.Name", "chain_6"}}),
#"Odstránené ostatné st?pce1" = Table.SelectColumns(#"Premenované st?pce4",{"Emp ID", "Name", "Reports to", "# of reports to my boss", "chain_2", "bosses_boss", "chain_3", "chain_4", "chain_5", "chain_6"}),
#"Duplicitný st?pec" = Table.DuplicateColumn(#"Odstránené ostatné st?pce1", "Name", "Name – kópia"),
#"Premenované st?pce5" = Table.RenameColumns(#"Duplicitný st?pec",{{"Name – kópia", "chain_1"}}),
#"St?pce so zmeneným poradím" = Table.ReorderColumns(#"Premenované st?pce5",{"Emp ID", "Name", "Reports to", "# of reports to my boss", "chain_1", "chain_2", "chain_3", "chain_4", "chain_5", "chain_6"}),
#"Extrahoval sa text pred odde?ova?om3" = Table.TransformColumns(#"St?pce so zmeneným poradím", {{"chain_1", each Text.BeforeDelimiter(_, " "), type text}}),
#"Extrahoval sa text pred odde?ova?om4" = Table.TransformColumns(#"Extrahoval sa text pred odde?ova?om3", {{"chain_5", each Text.BeforeDelimiter(_, " "), type text}}),
#"Extrahoval sa text pred odde?ova?om5" = Table.TransformColumns(#"Extrahoval sa text pred odde?ova?om4", {{"chain_6", each Text.BeforeDelimiter(_, " "), type text}}),
#"St?pce so zmeneným poradím1" = Table.ReorderColumns(#"Extrahoval sa text pred odde?ova?om5",{"Emp ID", "Name", "Reports to", "# of reports to my boss", "bosses_boss", "chain_1", "chain_2", "chain_3", "chain_4", "chain_5", "chain_6"}),
#"Zlú?ené st?pce" = Table.CombineColumns(#"St?pce so zmeneným poradím1",{"chain_1", "chain_2", "chain_3", "chain_4", "chain_5", "chain_6"},Combiner.CombineTextByDelimiter(">", QuoteStyle.None),"Zlú?ené"),
#"Zoradené riadky" = Table.Sort(#"Zlú?ené st?pce",{{"Emp ID", Order.Ascending}}),
#"Premenované st?pce6" = Table.RenameColumns(#"Zoradené riadky",{{"Zlú?ené", "chain"}})
in
#"Premenované st?pce6"
Thanks for the challenge - I enjoyed it! These are the simplest approaches I could come up with. For Q3, the nested XLOOKUPS took a minute to figure out, but they work. : )
Q1)
=COUNTIF([Reports to],[@[Reports to]])
Q2)
=IFERROR(XLOOKUP([@[Reports to]],[Emp ID],[Reports to]),"")
Q3)
=CONCATENATE(
TEXTBEFORE([@Name]," "),
IFERROR("->"&XLOOKUP([@[Reports to]],[Emp ID],TEXTBEFORE([Name]," ")),""),
IFERROR("->"&XLOOKUP([@[Boss''Boss]],[Emp ID],TEXTBEFORE([Name]," ")),""),
IFERROR("->"&XLOOKUP((XLOOKUP([@[Boss''Boss]],[Emp ID],[Reports to])),[Emp ID],TEXTBEFORE([Name]," ")),""),
IFERROR("->"&XLOOKUP((XLOOKUP((XLOOKUP([@[Boss''Boss]],[Emp ID],[Reports to])),[Emp ID],[Reports to])),[Emp ID],TEXTBEFORE([Name]," ")),""),
IFERROR("->"&XLOOKUP((XLOOKUP((XLOOKUP((XLOOKUP([@[Boss''Boss]],[Emp ID],[Reports to])),[Emp ID],[Reports to])),[Emp ID],[Reports to])),[Emp ID],TEXTBEFORE([Name]," ")),"")
)
2.
=INDEX(staff[Boss ID],MATCH(INDEX(staff[Boss ID],MATCH("TO-0021",staff[EmpID],0)),staff[EmpID]))
I have shared my answers at Linkedin :
https://www.linkedin.com/posts/jyoti-sharma7_who-is-my-bosss-boss-data-analytics-challenge-activity-7359673072301297666-9Msf?utm_source=share&utm_medium=member_desktop&rcm=ACoAAAQEBDYBiclkCbnnKCv0oPs0EdG85gxr6qQ
Check it out!
Thanks for the challenge Chandoo
1) SELECT b.EmpName AS Boss, COUNT(*) AS Subordinates
FROM Employee e
JOIN Employee b ON b.EmpID = e.MgrID
WHERE b.EmpID = 'TO-0002'
GROUP BY b.EmpID, b.EmpName;
2) WITH RecursiveBossChain AS (
SELECT employee_id, manager_id
FROM Employee
WHERE employee_id = 'TO-0021'
UNION ALL
SELECT e.employee_id, e.manager_id
FROM Employee e
JOIN RecursiveBossChain r ON e.employee_id = r.manager_id
)
SELECT manager_id
FROM RecursiveBossChain
WHERE employee_id = 'TO-0002';
3) WITH RECURSIVE EmployeeHierarchy AS (
-- Start with the employee
SELECT EmpID, EmpName, MgrID, CAST(EmpName AS VARCHAR(1000)) AS Chain
FROM Employee
WHERE EmpID = 'TO-0021'
UNION ALL
-- Recursively climb up
SELECT e.EmpID, e.EmpName, e.MgrID, eh.Chain || ' -> ' || e.EmpName
FROM Employee e
JOIN EmployeeHierarchy eh ON e.EmpID = eh.MgrID
)
SELECT Chain
FROM EmployeeHierarchy
WHERE MgrID IS NULL; -- Optionally stop at the top
Thank you for the challenge. It gave me a lot of joy and would like to know a more effective solution for number 3:-). What would I need to add to know how many managers variables to create, without any helper column?
1) =COUNTIFS([Reports to];[@[Reports to]])
2) =IF(XLOOKUP(XLOOKUP([@[Reports to]];[Emp ID];[Name];"");[Name];[Reports to];"")=0;"";XLOOKUP(XLOOKUP([@[Reports to]];[Emp ID];[Name];"");[Name];[Reports to];""))
3) =LET(
empID;staff[Emp ID];
Name;staff[Name];
reportsTo;staff[Reports to];
firstNames;TEXTBEFORE(Name;" ");
managers;XLOOKUP(FILTER(empID;ISNUMBER(XMATCH(empID;reportsTo));"");empID;Name;"");
managersID;XLOOKUP(managers;Name;empID;"");
manNames;TEXTBEFORE(managers;" ");
managers2;IF(XLOOKUP(managers;Name;reportsTo;"")=0;"";XLOOKUP(managers;Name;reportsTo;""));
man2Names;IF(managers2="";"";TEXTBEFORE(XLOOKUP(managers2;empID;Name);" "));
managers3;IF(XLOOKUP(managers2;empID;reportsTo;"")=0;"";XLOOKUP(managers2;empID;reportsTo;""));
man3Names;IF(managers3="";"";TEXTBEFORE(XLOOKUP(managers3;empID;Name);" "));
managers4;IF(XLOOKUP(managers3;empID;reportsTo;"")=0;"";XLOOKUP(managers3;empID;reportsTo;""));
man4Names;IF(managers4="";"";TEXTBEFORE(XLOOKUP(managers4;empID;Name);" "));
managers5;IF(XLOOKUP(managers4;empID;reportsTo;"")=0;"";XLOOKUP(managers4;empID;reportsTo;""));
man5Names;IF(managers5="";"";TEXTBEFORE(XLOOKUP(managers5;empID;Name);" "));
chain;HSTACK(manNames;man2Names;man3Names;man4Names;man5Names);
lookup;BYROW(chain;LAMBDA(row;TEXTJOIN("->";TRUE;row)));
IF(reportsTo="";firstNames;firstNames&"->"&XLOOKUP(reportsTo;managersID;lookup))
)
Question 1
COUNTIF([Reports to],[@[Reports to]])
Question 2:
LET(
BossesBossID, XLOOKUP(
XLOOKUP([@[Emp ID]], [Emp ID], [Reports to], "", 0),
[Emp ID],
[Reports to],
"No upper boss",
0
),
IF(BossesBossID = 0, "No upper boss", BossesBossID)
)
Question 3:
LET(
StartID, [@[Emp ID]],
IdRange, [Emp ID],
NameRange, [Name],
BossRange, [Reports to],
FirstName, LAMBDA(text, LEFT(text, FIND(" ", text & " ") - 1)),
Name1, FirstName(XLOOKUP(StartID, IdRange, NameRange, "Unknown")),
BossID1, XLOOKUP(StartID, IdRange, BossRange, ""),
Name2, IF(BossID1="", "", FirstName(XLOOKUP(BossID1, IdRange, NameRange, "."))),
BossID2, IF(BossID1="", "", XLOOKUP(BossID1, IdRange, BossRange, "")),
Name3, IF(BossID2="", "", FirstName(XLOOKUP(BossID2, IdRange, NameRange, "."))),
BossID3, IF(BossID2="", "", XLOOKUP(BossID2, IdRange, BossRange, "")),
Name4, IF(BossID3="", "", FirstName(XLOOKUP(BossID3, IdRange, NameRange, "."))),
BossID4, IF(BossID3="", "", XLOOKUP(BossID3, IdRange, BossRange, "")),
Name5, IF(BossID4="", "", FirstName(XLOOKUP(BossID4, IdRange, NameRange, "."))),
BossID5, IF(BossID4="", "", XLOOKUP(BossID4, IdRange, BossRange, "")),
Name6, IF(BossID5="", "", FirstName(XLOOKUP(BossID5, IdRange, NameRange, "."))),
CompleteChain, TEXTJOIN("->", TRUE, Name1, Name2, Name3, Name4, Name5, Name6),
IF(
RIGHT(CompleteChain, 2) = "Jo",
"",
CompleteChain
)
)
Thankk you for this challenge.
Javier
Hey Chandoo...
I think one "Roadmap" ? to success is to use PATH() and PATHITEM() in DAX. I think I learned this from you somewhere on the "Road to Awesome".
I've shared my answers in LinkedIn
https://www.linkedin.com/posts/al-jun-esguerra-53504a4b_who-is-my-bosss-boss-data-analytics-challenge-activity-7360317884792795138-dPku?utm_source=share&utm_medium=member_desktop&rcm=ACoAAAqBunkB2e-HydhmQHB5E7DosEu7c8HUVzM
Waiting for the next one.
Hello, chandoo,
I tried solving the problem in SQL. Here’s my approach for MySQL
-- How many people are directly reporting to my boss?
select e.reports_to,count(1) as direct_reports from staff_emp e
where e.reports_to=(select reports_to from staff_emp where emp_id='TO-0021')
group by e.reports_to;
-- For a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss. The answer should be TO-0007, Michael Scott.
-- t1=boss, t2=boss's boss
select b2.emp_id,b2.name from staff_emp e
join staff_emp b1 on e.reports_to=b1.emp_id
join staff_emp b2 on b1.reports_to=b2.emp_id
where e.emp_id='TO-0021';
-- What is my reporting chain?
select CONCAT(
SUBSTRING_INDEX(e.name, ' ', 1), '->',
SUBSTRING_INDEX(b1.name, ' ', 1), '->',
SUBSTRING_INDEX(b2.name, ' ', 1), '->',
SUBSTRING_INDEX(b3.name, ' ', 1), '->',
SUBSTRING_INDEX(b4.name, ' ', 1)
) AS reporting_chain from staff_emp e
join staff_emp b1 on e.reports_to=b1.emp_id
join staff_emp b2 on b1.reports_to=b2.emp_id
join staff_emp b3 on b2.reports_to=b3.emp_id
join staff_emp b4 on b3.reports_to=b4.emp_id
where e.emp_id='TO-0021';
Please let me know your feedback or if you see any improvements I can make.
Thank u for this challenge.
Hello Chandoo
Office 365 in French
1) =NB.SI([Reports to];[@[Reports to]])
2) =RECHERCHEX([@[Reports to]];[Emp ID];[Reports to];"")
3) =reportingChain(TEXTE.AVANT(staff[@Name]; " ");staff[@[Emp ID]])
With Lambda
=LET(
ReportsTo; RECHERCHEX(EmpID; staff[Emp ID]; staff[Reports to]; "");
SI(
ReportsTo = "";
FirstName;
FirstName & "->" &
reportingChain(
TEXTE.AVANT(RECHERCHEX(ReportsTo; staff[Emp ID]; staff[Name]); " ");
ReportsTo
)
)
)
Best regards
J-M
I did it in Oracle SQL using self join
SQL> select m.empname ,count(e.empid) from staff e,staff m where e.bossid = m.empid group by m.empname;
EMPNAME COUNT(E.EMPID)
-------------------- --------------
Jim HalPert 2
Pam bee 1
Michal 4
Dwight 1
Creed 1
Angela 1
6 rows selected.
SQL> select e.empid,e.empname,e.bossid,m.empname from staff e,staff m where e.bossid = m.empid;
EMPID EMPNAME BOSSID EMPNAME
---------- -------------------- ---------- --------------------
TO-0018 Andy TO-0002 Jim HalPert
TO-0021 Cathy TO-0002 Jim HalPert
TO-0009 Erin TO-0004 Pam bee
TO-0002 Jim HalPert TO-0007 Michal
TO-0015 Dwight TO-0007 Michal
TO-0028 Creed TO-0007 Michal
TO-0035 Angela TO-0007 Michal
TO-0004 Pam bee TO-0015 Dwight
TO-0031 Kelly TO-0028 Creed
TO-0024 Keveen TO-0035 Angela
SQL> select e.empid,e.empname,e.bossid,m.empname from staff e,staff m where e.bossid = m.empid(+);
EMPID EMPNAME BOSSID EMPNAME
---------- -------------------- ---------- --------------------
TO-0018 Andy TO-0002 Jim HalPert
TO-0021 Cathy TO-0002 Jim HalPert
TO-0009 Erin TO-0004 Pam bee
TO-0002 Jim HalPert TO-0007 Michal
TO-0015 Dwight TO-0007 Michal
TO-0028 Creed TO-0007 Michal
TO-0035 Angela TO-0007 Michal
TO-0004 Pam bee TO-0015 Dwight
TO-0031 Kelly TO-0028 Creed
TO-0024 Keveen TO-0035 Angela
TO-0007 Michal TO-0067
EMPID EMPNAME BOSSID EMPNAME
---------- -------------------- ---------- --------------------
TO-0010 Jan TO-0067
TO-0030 Tobby TO-0067
TO-0033 Robert TO-0067
14 rows selected.
I want to use unique ID CNIC and want to find any employee complete data
1- type campus show all data campus wise
2- type department show all employees data
3- Cadre name and department then show employees data
4- like many other desired fields
Solve my problems
Note:- formulas apply only main query file
I used my favorite data manipulation tool "Microsoft Excel" to get all the answers.
Question 1 (Answer) =COUNTIF($D$4:$D$31,$D$11) - D4:D31 is the Boss ID column and D11 is the "0002" ID; CountIf function was used to count the number of times "0002" appears in that column.
Question 2 (Answer) = =VLOOKUP($B$4,$B$3:$G$31,3,FALSE) - B4 is the lookup value i.e Employee ID, B3:G31 is the total table array, 3 is the index column number (Boss ID) column and False is for the exact match
Question 3 (Answer) = =INDEX($C$4:$C$31, MATCH(B12, $B$4:$B$31, 0))
& " -> " &
INDEX($C$4:$C$31, MATCH(INDEX($D$4:$D$31, MATCH(B12, $B$4:$B$31, 0)), $B$4:$B$31, 0))
& " -> " &
INDEX($C$4:$C$31, MATCH(INDEX($D$4:$D$31, MATCH(INDEX(D4:D31, MATCH(B12, $B$4:$B$31, 0)), $B$4:$B$31, 0)), $B$4:$B$31, 0))
& " -> " &
INDEX($C$4:$C$31, MATCH(INDEX($D$4:$D$31, MATCH(INDEX($D$4:$D$31, MATCH(INDEX($D$4:$D$31, MATCH(B12, $B$4:$B$31, 0)), $B$4:$B$31, 0)), $B$4:$B$31, 0)), $B$4:$B$31, 0))
& " -> " &
INDEX($C$4:$C$31, MATCH(INDEX($D$4:$D$31, MATCH(INDEX($D$4:$D$31, MATCH(INDEX($D$4:$D$31, MATCH(INDEX($D$4:$D$31, MATCH(B12, $B$4:$B$31, 0)), $B$4:$B$31, 0)), $B$4:$B$31, 0)), $B$4:$B$31, 0)), $B$4:$B$31, 0))
Explanation:
The reporting chain formula works by repeatedly using the INDEX and MATCH functions to look up an employee’s name from their ID, then use that ID to find their manager’s ID, and then look up that manager’s name, repeating the process for as many levels as needed. First, MATCH(EmpID_to_find, EmpID, 0) finds the row position of the employee in the EmpID range, and INDEX(EmpName, that_row) returns their name. To get the manager, INDEX(MgrID, MATCH(EmpID_to_find, EmpID, 0)) retrieves the manager’s ID from the MgrID range, and wrapping that in another INDEX/MATCH pair returns the manager’s name. Nesting this process allows you to step upward in the hierarchy, finding the manager’s manager, and so on, each time feeding the found manager ID back into another INDEX/MATCH to get the next name. The results for each level are then concatenated using " -> " so the final output reads like “Cathy Simms -> Jim Halpert -> Michael Scott -> Jo Bennet.” The limitation is that each additional hierarchy level must be manually added to the formula, so for a fully dynamic solution, Power Query or VBA would be more efficient.
With the data in the range A5:C33 and with row 5 containing the column headers. Here are my formulas
E4 contains a data validation cell with all Employee IDs in there
For Q1 E6=XLOOKUP(E4,A6:A33,B6:B33)
F6=XLOOKUP(E6,B6:B33,C6:C33)
G6=COUNTIFS(C6:C33,F6)
For Q2 and Q3 H7=LET(
emp, E4,
id, A6:A33,
name, B6:B33,
boss, C6:C33,
steps, ROWS(id),
ids, SCAN(emp, SEQUENCE(steps), LAMBDA(prev,_, XLOOKUP(prev, id, boss, ""))),
chainIDs, FILTER(ids, ids""),
XLOOKUP(chainIDs, id, name)
)
Final submission in E11=TEXTJOIN("->",,H7#)
I will also add that I created a pivot table from the input data with Rows=Employee ID, Columns=Boss ID and Values=Count of Name
Similarly, I created the following from the input data =PIVOTBY(relationships4[[#All],[Emp ID]],relationships4[[#All],[Boss ID]],relationships4[[#All],[Name]],COUNTA,3)
I have a query file of university employees in sheet-1
In sheet-2
A1 is empty
B1 is empty
C1 is empty
D1 is empty
1- If I write only Campus name in cell A1 then campus wise employees data should be shown.
2- If I write Campus name in cell A1 and Faculty name in cell B1 then campus wise & Department wise employees data should be shown
3- If I write Campus name in cell A1 and Faculty name in cell B1 and then Department name in C1 then campus wise, Faculty wise & Department wise employees data should be shown
4- so on
3. Used in-line recursive Lambda.
=LAMBDA(empID,
LET(getChainName,
LAMBDA(self,empID,
LET(bossName, TEXTBEFORE(XLOOKUP(empID, staff[Emp ID], staff[Name], "")," "),
bossID, XLOOKUP(empID, staff[Emp ID], staff[Reports to], ""),
IF( bossID = "", bossName, bossName & "->" & self(self, bossID) )
)
),
getChainName(getChainName, empID)
)
)("TO-0021")
1 For a given employee ID (say TO-0021, Cathy Simms), how many people are directly reporting to their boss (TO-0002, Jim Halpert)?
TO-0021
3 =COUNTIF(B4:G30,VLOOKUP(J7,staff,3))
2 The second question is for a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss.
TO-0021
TO-0007 =VLOOKUP(VLOOKUP(J12,staff,3),staff,3)
3 For a given employee (say TO-0021, Cathy Simms), print their entire reporting chain, delimited by the symbol ->.
I don't know ,I have EXCEL-2019
-- 1) How many people are directly reporting to my boss?
/* select
bossid,
count(distinct empid) as reportees_num
from employees
group by 1
order by 1
*/
-- 2) Who is my boss’s boss?
/* select e.empid,
coalesce(boss2.name,"not mentioned") as bosses_boss
from employees e
join employees boss1
on e.bossid = boss1.empid
join employees boss2
on boss1.bossid = boss2.empid
where e.empid = 'TO-0021'
order by 1
*/
-- 3) to find the full reporting chain for a given employee.
/*
WITH RECURSIVE reporting_chain AS (
-- The Anchor Member: This is the starting point of the recursion.
-- It selects the employee with EmpID 'TO-0021' and initializes
-- the 'Chain' column with their name.
SELECT
EmpID,
Name,
BossID,
CAST(Name AS VARCHAR(255)) AS Chain
FROM
employees
WHERE
EmpID = 'TO-0021'
-- The UNION ALL operator combines the anchor member with the
-- recursive member.
UNION ALL
-- The Recursive Member: This part joins the CTE with the
-- 'employees' table to find the next person up the chain.
SELECT
e.EmpID,
e.Name,
e.BossID,
CAST(rc.Chain || '->' || e.Name AS VARCHAR(255)) AS Chain
FROM
employees AS e
INNER JOIN
reporting_chain AS rc ON e.EmpID = rc.BossID
)
-- The final SELECT statement retrieves the full reporting chain.
-- We order by the length of the chain to get the final, longest chain.
SELECT
Chain
FROM
reporting_chain
ORDER BY
LENGTH(Chain) DESC
LIMIT 1;
*/
1) =COUNTIF([Reports to],[@[Reports to]])
2) =LET(
Boss1No,[@[Reports to]],
Boss1Name,XLOOKUP(Boss1No,[Emp ID],[Name],""),
Boss1Name
)
3) I wanted to do this in Excel. I knew a general algorithm would need an iterative solution with the exit condition of Boss = "". Recursion is probably the best solution: easy in VBA but I didn't know how to do it in Excel. I suspected LAMBDA would be involved, which the first comment confirms.
I was only recently introduced to LET so this is my inelegant solution. I assumed 7 nodes max., as suggested. An interesting exercise!
=LET(
EmpFirstName, TEXTBEFORE([@Name]," ",,,,""),
Boss1No,[@[Reports to]],
Boss1FirstName,TEXTBEFORE(XLOOKUP(Boss1No,[Emp ID],[Name],"")," ",,,,""),
Boss2No,XLOOKUP(Boss1No,[Emp ID],[Reports to],""),
Boss2FirstName,TEXTBEFORE(XLOOKUP(Boss2No,[Emp ID],[Name],"")," ",,,,""),
Boss3No,XLOOKUP(Boss2No,[Emp ID],[Reports to],""),
Boss3FirstName,TEXTBEFORE(XLOOKUP(Boss3No,[Emp ID],[Name],"")," ",,,,""),
Boss4No,XLOOKUP(Boss3No,[Emp ID],[Reports to],""),
Boss4FirstName,TEXTBEFORE(XLOOKUP(Boss4No,[Emp ID],[Name],"")," ",,,,""),
Boss5No,XLOOKUP(Boss4No,[Emp ID],[Reports to],""),
Boss5FirstName,TEXTBEFORE(XLOOKUP(Boss5No,[Emp ID],[Name],"")," ",,,,""),
Boss6No,XLOOKUP(Boss5No,[Emp ID],[Reports to],""),
Boss6FirstName,TEXTBEFORE(XLOOKUP(Boss6No,[Emp ID],[Name],"")," ",,,,""),
DeLim, "->",
IF(Boss1FirstName="", EmpFirstName,
IF(Boss2FirstName="",CONCAT(EmpFirstName, DeLim, Boss1FirstName),
IF(Boss3FirstName="", CONCAT(EmpFirstName, DeLim, Boss1FirstName, DeLim, Boss2FirstName),
IF(Boss4FirstName="", CONCAT(EmpFirstName, DeLim, Boss1FirstName, DeLim, Boss2FirstName, DeLim, Boss3FirstName),
IF(Boss5FirstName="", CONCAT(EmpFirstName, DeLim, Boss1FirstName, DeLim, Boss2FirstName, DeLim, Boss3FirstName, DeLim, Boss4FirstName),
IF(Boss6FirstName="", CONCAT(EmpFirstName, DeLim, Boss1FirstName, DeLim, Boss2FirstName, DeLim, Boss3FirstName, DeLim, Boss4FirstName, DeLim, Boss5FirstName),
CONCAT(EmpFirstName, DeLim, Boss1FirstName, DeLim, Boss2FirstName, DeLim, Boss3FirstName, DeLim, Boss4FirstName, DeLim, Boss5FirstName, DeLim, Boss6FirstName)
)
)
)
)
)
)
)
1
=TEXTJOIN(",",TRUE,FILTER(B:B,D:D=D12)) , =COUNTIF(D:D,D12)
2 =TEXTJOIN(",",TRUE,FILTER(D:D,B:B=D12))
3 =TEXTJOIN(",",TRUE,FILTER($D:$D,$B:$B=B12))
TEXTJOIN(",",TRUE,FILTER($D:$D,$B:$B=L18))
TEXTJOIN(",",TRUE,FILTER($D:$D,$B:$B=L19))
TEXTJOIN(",",TRUE,FILTER($D:$D,$B:$B=L20))
C12&"->"&K18&"->"&K19&"->"&K20&"->"&K21
1. How many report to my boss
=COUNTIFS([Reports to],[@[Reports to]])
2. Whose my boss's boss
=XLOOKUP([@[Reports to]],[Emp ID],[Reports to])
3. Whose in my boss-chain
=LET(
empID, G16,
empName, XLOOKUP(empID, data[Emp ID], data[Name]),
maxLevel, G15,
TEXTJOIN(
">", TRUE,
VSTACK(
empName,
BYROW(
SCAN(
empID,
SEQUENCE(maxLevel),
LAMBDA(acc, val, XLOOKUP(acc, data[Emp ID], data[Reports to], "") ) ),
LAMBDA(x, XLOOKUP(x, data[Emp ID], data[Name], "") ) ) ) ) )
1.let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Reports to"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Reports to", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Reports to"}, #"Empp ID", {"Emp ID"}, "Empp ID", JoinKind.LeftOuter),
#"Expanded Empp ID" = Table.ExpandTableColumn(#"Merged Queries", "Empp ID", {"Name"}, {"Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Empp ID",{"Name", "Reports to", "Count"}),
#"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"Reports to", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Job", each "Boss"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Reports to", "Name", "Count", "Job"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Name] null))
in
#"Filtered Rows"
2.let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Reports to"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Emp ID"}, #"ID BOSS (how many Answer)", {"Reports to"}, "ID BOSS", JoinKind.LeftOuter),
#"Expanded ID BOSS" = Table.ExpandTableColumn(#"Merged Queries", "ID BOSS", {"Job"}, {"Job"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded ID BOSS",null,"Employee",Replacer.ReplaceValue,{"Job"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Job", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Job] = "Employee")),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Emp ID", Order.Ascending}}),
#"Merged Queries1" = Table.NestedJoin(#"Sorted Rows1", {"Emp ID"}, #"Empp ID report to boss", {"Emp ID"}, "Empp ID report to boss", JoinKind.LeftOuter),
#"Expanded Empp ID report to boss" = Table.ExpandTableColumn(#"Merged Queries1", "Empp ID report to boss", {"Reports to"}, {"Reports to"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Empp ID report to boss", {"Reports to"}, #"Empp ID", {"Emp ID"}, "Empp ID", JoinKind.LeftOuter),
#"Expanded Empp ID" = Table.ExpandTableColumn(#"Merged Queries2", "Empp ID", {"Name"}, {"Name.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Empp ID",{{"Name.1", "Boss 1"}}),
#"Merged Queries3" = Table.NestedJoin(#"Renamed Columns", {"Reports to"}, #"Empp ID report to boss", {"Emp ID"}, "Empp ID report to boss", JoinKind.LeftOuter),
#"Expanded Empp ID report to boss1" = Table.ExpandTableColumn(#"Merged Queries3", "Empp ID report to boss", {"Reports to"}, {"Reports to.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Empp ID report to boss1",{{"Reports to.1", "Reports to 1"}}),
#"Merged Queries4" = Table.NestedJoin(#"Renamed Columns1", {"Reports to 1"}, #"Empp ID", {"Emp ID"}, "Empp ID", JoinKind.LeftOuter),
#"Expanded Empp ID1" = Table.ExpandTableColumn(#"Merged Queries4", "Empp ID", {"Name"}, {"Name.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Empp ID1",{{"Name.1", "Boss 2"}, {"Reports to 1", "Reports to 2"}}),
#"Merged Queries5" = Table.NestedJoin(#"Renamed Columns2", {"Reports to 2"}, #"Empp ID report to boss", {"Emp ID"}, "Empp ID report to boss", JoinKind.LeftOuter),
#"Expanded Empp ID report to boss2" = Table.ExpandTableColumn(#"Merged Queries5", "Empp ID report to boss", {"Reports to"}, {"Reports to.1"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Empp ID report to boss2",{{"Reports to.1", "Reports to 3"}}),
#"Merged Queries6" = Table.NestedJoin(#"Renamed Columns3", {"Reports to 3"}, #"Empp ID", {"Emp ID"}, "Empp ID", JoinKind.LeftOuter),
#"Expanded Empp ID2" = Table.ExpandTableColumn(#"Merged Queries6", "Empp ID", {"Name"}, {"Name.1"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded Empp ID2",{{"Name.1", "Boss 3"}, {"Reports to", "ID Boss 1"}, {"Reports to 2", "ID Boss 2"}, {"Reports to 3", "ID Boss 3"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns4",{"Emp ID", "Name", "ID Boss 1", "Boss 1"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Other Columns",{{"ID Boss 1", "ID Boss"}, {"Boss 1", "Boss'Boss"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns5", #"Boss'Boss 2"})
in
#"Appended Query"
3.let
Source = Table.Combine({#"Chain emp", #"Chain boss"}),
#"Sorted Rows" = Table.Sort(Source,{{"ID Emp", Order.Ascending}})
in
#"Sorted Rows"
CREATE TABLE employee (
employee_id VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
boss_id VARCHAR(10) NULL
);
INSERT INTO employee (employee_id, name, boss_id) VALUES
('TO-0002', 'Jim Halpert', 'TO-0007'),
('TO-0004', 'Pam Beesly', 'TO-0015'),
('TO-0007', 'Michael Scott', 'TO-0067'),
('TO-0009', 'Erin Hannon', 'TO-0004'),
('TO-0010', 'Jan Levinson', 'TO-0067'),
('TO-0013', 'Karen Filippelli', 'TO-0076'),
('TO-0015', 'Dwight Schrute', 'TO-0007'),
('TO-0018', 'Andy Bernard', 'TO-0002'),
('TO-0021', 'Cathy Simms', 'TO-0002'),
('TO-0024', 'Kevin Malone', 'TO-0035'),
('TO-0028', 'Creed Bratton', 'TO-0007'),
('TO-0030', 'Toby Flenderson', 'TO-0067'),
('TO-0031', 'Kelly Kapoor', 'TO-0028'),
('TO-0033', 'Robert California', 'TO-0067'),
('TO-0035', 'Angela Martin', 'TO-0007'),
('TO-0039', 'Stanley Hudson', 'TO-0007'),
('TO-0043', 'Ryan Howard', 'TO-0031'),
('TO-0046', 'Oscar Martinez', 'TO-0035'),
('TO-0050', 'Meredith Palmer', 'TO-0028'),
('TO-0051', 'Gabe Lewis', 'TO-0072'),
('TO-0055', 'Phyllis Vance', 'TO-0030'),
('TO-0059', 'Roy Anderson', 'TO-0004'),
('TO-0063', 'Nellie Bertram', 'TO-0030'),
('TO-0066', 'Darryl Philbin', 'TO-0039'),
('TO-0067', 'David Wallace', 'TO-0072'),
('TO-0068', 'Pete Miller', 'TO-0015'),
('TO-0072', 'Jo Bennet', NULL),
('TO-0076', 'Todd Parker', 'TO-0002');
# Q1.
SELECT COUNT(*) AS direct_reports_count
FROM employee
WHERE boss_id = (
SELECT boss_id
FROM employee
WHERE employee_id = 'TO-0021'
);
# Q2.
SELECT g.employee_id AS grandboss_id, g.name AS grandboss_name
FROM employee e
LEFT JOIN employee b ON b.employee_id = e.boss_id
LEFT JOIN employee g ON g.employee_id = b.boss_id
WHERE e.employee_id = 'TO-0021';
# Q3.
SELECT
CONCAT(e.name, ' -> ', b1.name, ' -> ', b2.name, ' -> ', b3.name, ' -> ', b4.name) AS chain
FROM employee e
LEFT JOIN employee b1 ON b1.employee_id = e.boss_id
LEFT JOIN employee b2 ON b2.employee_id = b1.boss_id
LEFT JOIN employee b3 ON b3.employee_id = b2.boss_id
LEFT JOIN employee b4 ON b4.employee_id = b3.boss_id
WHERE e.employee_id = 'TO-0021';
===========================================
Q1) How many people are directly reporting to my boss? (Score = 5pts)
===========================================
import pandas as pd
# Load the CSV created from the image (uploaded by user)
csv_path = "/mnt/data/employees_from_image_clean.csv"
df = pd.read_csv(csv_path)
# Step 1: Employee info
employee_id = "TO-0021"
employee_name = df.loc[df["Emp ID"] == employee_id, "Name"].iloc[0]
# Step 2: Find Boss ID and Name
boss_id = df.loc[df["Emp ID"] == employee_id, "Boss ID"].iloc[0]
boss_name = df.loc[df["Emp ID"] == boss_id, "Name"].iloc[0]
# Step 3: Count how many report to that boss
direct_reports = df[df["Boss ID"] == boss_id].shape[0]
# Step 4: Human-friendly output
output_message = (
f"{employee_name} (ID: {employee_id}) reports to {boss_name} (ID: {boss_id}). "
f"{boss_name} has {direct_reports} direct reports, including {employee_name}."
)
output_message
========================================
====== Q1) How many people are directly reporting ====
====== to my boss? (Score = 5pts) ====
========================================
import pandas as pd
# Load the CSV created from the image (uploaded by user)
csv_path = "/mnt/data/employees_from_image_clean.csv"
df = pd.read_csv(csv_path)
# Step 1: Employee info
employee_id = "TO-0021"
employee_name = df.loc[df["Emp ID"] == employee_id, "Name"].iloc[0]
# Step 2: Find Boss ID and Name
boss_id = df.loc[df["Emp ID"] == employee_id, "Boss ID"].iloc[0]
boss_name = df.loc[df["Emp ID"] == boss_id, "Name"].iloc[0]
# Step 3: Count how many report to that boss
direct_reports = df[df["Boss ID"] == boss_id].shape[0]
# Step 4: Human-friendly output
output_message = (
f"{employee_name} (ID: {employee_id}) reports to {boss_name} (ID: {boss_id}). "
f"{boss_name} has {direct_reports} direct reports, including {employee_name}."
)
output_message
Looks like this is made by AI. Can you confirm if you wrote the code?
1. =ROWS(FILTER([Emp ID],[Reports to]=[@[Reports to]]))
2. =LET(
boss,XLOOKUP([@[Reports to]],[Emp ID],[Reports to],""),
IF(LEN(boss)=0,"",boss)
)
3. =LET(
custom,LAMBDA(ME,node,hier,
IF(LEN(node)=0,
hier,
ME(ME,
XLOOKUP(node,[Emp ID],[Reports to]),
VSTACK(hier,node)
))),
chain,custom(custom,[@[Reports to]],[@[Emp ID]]),
TEXTJOIN("->",,TEXTBEFORE(XLOOKUP(chain,[Emp ID],[Name])," "))
)
And to have the reporting chain go top-down instead of bottom-up, just switch the two pieces of the stack: VSTACK(node,hier).
I started by writing Lambda functions BOSS? and STAFF? to look up the boss and list of staff respectively in the reporting chain centred upon the individual. I tried to avoid overt use of the primary and foreign keys and work with the name field.
BOSS? = LAMBDA(name, [dummy],
LET(
boss?sID, XLOOKUP(name, empName, boss, ""),
bossName, IF(boss?sID"", XLOOKUP(boss?sID, empID, empName, ""), ""),
IF(dummy, bossName, boss?sID)
)
);
That gets tricky in that it has to account for there being not boss for the most senior staff member and, perversely, one of the questions expects an employee ID to be returned in place of a name.
The function to return the boss's boss might be
BOSS²? = LAMBDA(name,
REDUCE(name, {1,0}, BOSS?)
);
Conversely, the function to return staff reporting to a given individual is a little more straightforward
STAFF? = LAMBDA(name, [option],
FILTER(empName, boss = XLOOKUP(name, empName, empID))
);
though is complicated by the need to return the count of staff members
STAFFCOUNT? = LAMBDA(name, COUNTA(STAFF?(name)));
One's boss's boss might be given by
BOSS²? = LAMBDA(name,
REDUCE(name, {1,0}, BOSS?)
);
or by simply nesting the BOSS? function.
To return an entire reporting chain again requires recursion, here achieved with SCAN
CHAIN? = LAMBDA(name,
LET(
reportingChain, VSTACK(name, SCAN(name, SEQUENCE(7), BOSS?)),
firstNames, TOROW(TEXTBEFORE(reportingChain," ",1,,1," "), 3),
returnString, TEXTJOIN("->", , firstNames),
returnString
)
);
Once that little lot is in place the dynamic array version of the solution requires worksheet formulae
= MAP(BOSS?(staff[Name], 1), STAFFCOUNT?)
= BOSS²?(staff[Name])
= MAP(staff[Name], CHAIN?)
As an alternative, one might use single cell array formulae within the Table
= STAFFCOUNT?(BOSS?([@Name], 1))
= BOSS²?([@Name])
= CHAIN?([@Name])
Perhaps this does not form the best introduction to an article on PowerQuery, but I tend only to switch platforms if essential.
I used Power Query. Loaded staff and duplicated it twice.
First Query:
let
Source = Excel.CurrentWorkbook(){[Name="staff"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", type text}, {"Name", type text}, {"Reports to", type text}, {"# Reports to my boss", type any}, {"Boss'Boss", type text}, {"Chain", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"# Reports to my boss", "Boss'Boss", "Chain"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Name", each Text.BeforeDelimiter(_, " "), type text}})
in
#"Extracted Text Before Delimiter"
Second Query
let
Source = Excel.CurrentWorkbook(){[Name="staff"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", type text}, {"Name", type text}, {"Reports to", type text}, {"# Reports to my boss", type any}, {"Boss'Boss", type text}, {"Chain", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Reports to"}, {{"Number of reportees", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Third Query
let
Source = Excel.CurrentWorkbook(){[Name="staff"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", type text}, {"Name", type text}, {"Reports to", type text}, {"# Reports to my boss", type any}, {"Boss'Boss", type text}, {"Chain", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"# Reports to my boss", "Boss'Boss", "Chain"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Name", each Text.BeforeDelimiter(_, " "), type text}}),
#"Merged Queries" = Table.NestedJoin(#"Extracted Text Before Delimiter", {"Reports to"}, staff, {"Emp ID"}, "staff", JoinKind.LeftOuter),
#"Expanded staff" = Table.ExpandTableColumn(#"Merged Queries", "staff", {"Name", "Reports to"}, {"Name.1", "Reports to.1"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded staff", {"Reports to.1"}, staff, {"Emp ID"}, "staff", JoinKind.LeftOuter),
#"Expanded staff1" = Table.ExpandTableColumn(#"Merged Queries1", "staff", {"Name", "Reports to"}, {"Name.2", "Reports to.2"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded staff1", {"Reports to.2"}, staff, {"Emp ID"}, "staff", JoinKind.LeftOuter),
#"Expanded staff2" = Table.ExpandTableColumn(#"Merged Queries2", "staff", {"Name", "Reports to"}, {"Name.3", "Reports to.3"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded staff2", {"Reports to.3"}, staff, {"Emp ID"}, "staff", JoinKind.LeftOuter),
#"Expanded staff3" = Table.ExpandTableColumn(#"Merged Queries3", "staff", {"Name", "Reports to"}, {"Name.4", "Reports to.4"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded staff3", {"Reports to.4"}, staff, {"Emp ID"}, "staff", JoinKind.LeftOuter),
#"Expanded staff4" = Table.ExpandTableColumn(#"Merged Queries4", "staff", {"Name"}, {"Name.5"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded staff4", "Report Chain", each Text.Combine({[Name], [Name.1], [Name.2], [Name.3], [Name.4], [Name.5]}, "->"), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Name.1", "Name.2", "Reports to.2", "Name.3", "Reports to.3", "Name.4", "Reports to.4", "Name.5"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Boss's Boss", each if [Reports to.1] = null then [Reports to] else [Reports to]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column",{"Reports to.1"}),
#"Merged Queries5" = Table.NestedJoin(#"Removed Columns2", {"Reports to"}, #"Number of Employees", {"Reports to"}, "Number of Employees", JoinKind.LeftOuter),
#"Expanded Number of Employees" = Table.ExpandTableColumn(#"Merged Queries5", "Number of Employees", {"Number of reportees"}, {"Number of reportees"})
in
#"Expanded Number of Employees"
Great test, especially for no. 3. For said question, there are multiple ways of getting to the answer, but the train of thought was to build a model that can be dynamic enough to handle huge and changing datasets. Hence the Power Query approach was undertaken. Here's my code:
let
Source = staff,
#"Removed Columns" = Table.RemoveColumns(Source,{"# Reports to my boss", "Boss'Boss", "Chain"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Reports to"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff" = Table.ExpandTableColumn(#"Merged Queries", "dt_Staff", {"Reports to"}, {"Reports to.1"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded dt_Staff", {"Reports to.1"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff1" = Table.ExpandTableColumn(#"Merged Queries1", "dt_Staff", {"Reports to"}, {"Reports to.2"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded dt_Staff1", {"Reports to.2"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff2" = Table.ExpandTableColumn(#"Merged Queries2", "dt_Staff", {"Reports to"}, {"Reports to.3"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded dt_Staff2", {"Reports to.3"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff3" = Table.ExpandTableColumn(#"Merged Queries3", "dt_Staff", {"Reports to"}, {"Reports to.4"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded dt_Staff3", {"Reports to.4"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff4" = Table.ExpandTableColumn(#"Merged Queries4", "dt_Staff", {"Reports to"}, {"Reports to.5"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Expanded dt_Staff4", "Name1", each Text.BeforeDelimiter([Name], " "), type text),
#"Merged Queries5" = Table.NestedJoin(#"Inserted Text Before Delimiter", {"Reports to"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff5" = Table.ExpandTableColumn(#"Merged Queries5", "dt_Staff", {"First"}, {"First"}),
#"Merged Queries6" = Table.NestedJoin(#"Expanded dt_Staff5", {"Reports to.1"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff6" = Table.ExpandTableColumn(#"Merged Queries6", "dt_Staff", {"First"}, {"First.1"}),
#"Merged Queries7" = Table.NestedJoin(#"Expanded dt_Staff6", {"Reports to.2"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff7" = Table.ExpandTableColumn(#"Merged Queries7", "dt_Staff", {"First"}, {"First.2"}),
#"Merged Queries8" = Table.NestedJoin(#"Expanded dt_Staff7", {"Reports to.3"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff8" = Table.ExpandTableColumn(#"Merged Queries8", "dt_Staff", {"First"}, {"First.3"}),
#"Merged Queries10" = Table.NestedJoin(#"Expanded dt_Staff8", {"Reports to.4"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff10" = Table.ExpandTableColumn(#"Merged Queries10", "dt_Staff", {"First"}, {"First.4"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded dt_Staff10",{{"Reports to.1", "Rollup2"}, {"Reports to.2", "Rollup3"}, {"Reports to.3", "Rollup4"}, {"Reports to.4", "Rollup5"}, {"Reports to.5", "Rollup6"}, {"Reports to", "Rollup1"}, {"First", "Name2"}, {"First.1", "Name3"}, {"First.2", "Name4"}, {"First.3", "Name5"}, {"First.4", "Name6"}}),
#"Merged Queries9" = Table.NestedJoin(#"Renamed Columns", {"Rollup6"}, dt_Staff, {"Emp ID"}, "dt_Staff", JoinKind.LeftOuter),
#"Expanded dt_Staff9" = Table.ExpandTableColumn(#"Merged Queries9", "dt_Staff", {"First"}, {"First"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded dt_Staff9",{{"First", "Name7"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",null,"",Replacer.ReplaceValue,{"Rollup1", "Rollup2", "Rollup3", "Rollup4", "Rollup5", "Rollup6", "Name1", "Name2", "Name3", "Name4", "Name5", "Name6","Name7"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "C1", each if [Name1] = "" then "" else "->"),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "C2", each if [Name2] = "" then "" else "->"),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "C3", each if [Name3] = "" then "" else "->"),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "C4", each if [Name4] = "" then "" else "->"),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "C5", each if [Name5] = "" then "" else "->"),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "C6", each if [Name6] = "" then "" else "->"),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "C7", each if [Name7] = "" then "" else "->"),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column6", "Combo", each [Name1]&[C2]&[Name2]&[C3]&[Name3]&[C4]&[Name4]&[C5]&[Name5]&[C6]&[Name6]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Rollup2", "Rollup3", "Rollup4", "Rollup5", "Rollup6", "Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "C1", "C2", "C3", "C4", "C5", "C6", "Name7", "C7"})
in
#"Removed Columns1"
I have shared my file in mail box.
1. How many people are directly reporting to my boss?
=COUNTIF([Reports to],[@[Reports to]])
RESULT = 5, 2, 4, 2, 4, 1, 5, 3, 3, 2, 5, 4, 2, 4, 5, 5, 1, 2, 2, 2, 2, 2, 2, 1, 2, 2, 0, 3
2. Who is my boss’s boss?
=INDEX([Reports to],MATCH([@[Reports to]],[Emp ID],0))
RESULT = TO-0067, TO-0007, TO-0072, TO-0015, TO-0072, TO-0002, TO-0067, TO-0007, TO-0007, TO-0007, TO-0067, TO-0072, TO-0007, TO-0072, TO-0067, TO-0067, TO-0028, TO-0007, TO-0007, 0, TO-0067, TO-0015, TO-0067, TO-0007, 0, TO-0007, TO-0007
3. What is my reporting chain?
Reporting Level-1,
=INDEX(staff[[Emp ID]:[Reports to]],MATCH(staff[@[Reports to]],staff[Emp ID],0),2)
Reporting Level-2,
=INDEX(staff[[Emp ID]:[Boss''Boss]],MATCH(staff[@[Boss''Boss]],staff[Emp ID],0),2)
.
.
.
Reporting Level-5
Order of Employees by Reporting Levels (Full Names)
=TEXTJOIN(", ",TRUE,IFERROR(C4,""),IFERROR(I4,""),IFERROR(J4,""),IFERROR(L4,""),IFERROR(N4,""),IFERROR(P4,""))
Only First Name,
=TEXTJOIN("->", TRUE, TEXTBEFORE(TRIM(TEXTSPLIT(R4, ",")), " "))
RESULT =
Jim->Michael->David->Jo
Pam->Dwight->Michael->David->Jo
Michael->David->Jo
Erin->Pam->Dwight->Michael->David->Jo
Jan->David->Jo
Karen->Todd->Jim->Michael->David->Jo
Dwight->Michael->David->Jo
Andy->Jim->Michael->David->Jo
Cathy->Jim->Michael->David->Jo
Kevin->Angela->Michael->David->Jo
Creed->Michael->David->Jo
Toby->David->Jo
Kelly->Creed->Michael->David->Jo
Robert->David->Jo
Angela->Michael->David->Jo
Stanley->Michael->David->Jo
Ryan->Kelly->Creed->Michael->David->Jo
Oscar->Angela->Michael->David->Jo
Meredith->Creed->Michael->David->Jo
Gabe->Jo
Phyllis->Toby->David->Jo
Roy->Pam->Dwight->Michael->David->Jo
Nellie->Toby->David->Jo
Darryl->Stanley->Michael->David->Jo
David->Jo
Pete->Dwight->Michael->David->Jo
Jo
Todd->Jim->Michael->David->Jo
OK, first time using the site after my tutor recommended it.
Havent quite hit 3 but heres my attempt
1. =COUNTIF([Reports to],[@[Reports to]])
2. =XLOOKUP([@[Reports to]],[Emp ID],[Reports to],"")
have done the text split, and ended up limiting it to 5 but did
3. =[@Name]&"->"
&(XLOOKUP([@[Reports to]],[Emp ID],[Name],""))
&"->"
&(XLOOKUP(XLOOKUP([@[Reports to]],[Emp ID],[Reports to],""),[Emp ID],[Name],""))
&"->"
&(XLOOKUP(XLOOKUP(XLOOKUP([@[Reports to]],[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Name],""))
&"->"
&(XLOOKUP(XLOOKUP(XLOOKUP(XLOOKUP([@[Reports to]],[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Name],""))
&"->"
&(XLOOKUP(XLOOKUP(XLOOKUP(XLOOKUP(XLOOKUP([@[Reports to]],[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Reports to],""),[Emp ID],[Name],""))
which kind of worked, but is kind of messy
Really enjoyed the challenge. Thanks for sharing.
1. =COUNTIF([Reports to],[@[Reports to]]) in excel
2. =XLOOKUP([@[Reports to]],[Emp ID], [Reports to]) in excel
3. Created a named function with the formula below in google sheets:
=LET(reports_to, XLOOKUP(XLOOKUP(name, name_range, report_range,""), id_range, name_range, ""),
IF(OR(reports_to="", reports_to=name), LEFT(name,FIND(" ",name)), LEFT(name,FIND(" ",name)) & " -> " & RCHAIN(reports_to, id_range, name_range, report_range))
)
Executed the function as shown below:
=RCHAIN(B4,staff[Emp ID],staff[Name],staff[Reports to])
1. =COUNTIF(staff[Reports to],staff[@[Emp ID]]) -- 3
2. =XLOOKUP(D4,staff[Emp ID],staff[Name]) -- Michael Scott
3. =FILTER(staff[Name],staff[Reports to]=B4,"") --
Andy Bernard
Cathy Simms
Todd Parker
Good challenges.
Answers:
1. How may people ar directly reportin to my boss? I used =CONTAR.SI.CONJUNTO($D$4:$D$31;$D4)
2. Who is my Boss's? I used
=SI(SI.ND(BUSCARV($D4;$B$4:$D$31;3;0);"")0;SI.ND(BUSCARV($D4;$B$4:$D$31;3;0);"");D4)
3. What is my reportin chain? I used a LAMBDA Recursive function
=LAMBDA(empID;rangoEmp;rangoBoss;rangoName;
LET(
idx; COINCIDIR(empID; rangoEmp; 0);
padre; INDICE(rangoBoss; idx);
name; INDICE(rangoName; idx);
apodo; IZQUIERDA(name;HALLAR(" ";name)-1);
resultado; SI(
O(Boss = ""; ESBLANCO(Boss));
empID & " " & apodo;
empID & " " & apodo & " ? " & BuscarCadena(Boss; rangoEmp; rangoBoss; rangoName)
);
resultado
)
)