Do the Indexing for project, task and activity. Task indexing needs to be done for combination of Project & Task. Activity indexing needs to be done for combination of project, Task & activity.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 221
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Hierarchical Project Indexing with Power Query
Power Query solution 1 for Hierarchical Project Indexing, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
F = (p, t, r) => Text.Combine({p, Text.From(Table.PositionOf(Table.Distinct(t), r) + 1)}, "."),
P = Table.AddColumn(
Source,
"Project_Index",
each F(null, Source[[Project]], [Project = [Project]])
),
T = Table.AddColumn(
P,
"Task_Index",
each F(
[Project_Index],
Table.SelectRows(Source, (r) => r[Project] = [Project])[[Task]],
[Task = [Task]]
)
),
A = Table.AddColumn(
T,
"Activity_Index",
each F(
[Task_Index],
Table.SelectRows(Source, (r) => r[Project] = [Project] and r[Task] = [Task])[[Activity]],
[Activity = [Activity]]
)
)
in
APower Query solution 2 for Hierarchical Project Indexing, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "I"),
C = Table.AddColumn(B, "Project_Index", each Character.ToNumber([Project]) - 64),
D = Table.AddColumn(
C,
"Task_Index",
each [Project_Index] + (Character.ToNumber(Text.End([Task], 1)) - 64) / 10
),
E = Table.Group(
D,
{"Task"},
{
"All",
(x) =>
Table.AddColumn(
x,
"Activity_Index",
each Text.From([Task_Index])
& "."
& Text.From(List.PositionOf(List.Distinct(x[Activity]), [Activity]) + 1)
)
}
)[All],
F = Table.Combine(E),
G = Table.Sort(F, {"I", 0}),
H = Table.RemoveColumns(G, {"I"})
in
HPower Query solution 3 for Hierarchical Project Indexing, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddColumn = Table.AddColumn(
Source,
"Data",
each [
a = Text.From(List.PositionOf(List.Distinct(Source[Project]), [Project]) + 1),
b = Table.SelectRows(Source, (x) => x[Project] = [Project])[Task],
c = Text.From(List.PositionOf(List.Distinct(b), [Task]) + 1),
d = Table.SelectRows(Source, (x) => x[Project] = [Project] and x[Task] = [Task])[Activity],
e = Text.From(List.PositionOf(List.Distinct(d), [Activity]) + 1),
f = Text.Combine({a, Text.Combine({a, c}, "."), Text.Combine({a, c, e}, ".")}, ", ")
][f]
),
Columns = List.Transform(Table.ColumnNames(Source), each _ & "_Index"),
Result = Table.SplitColumn(AddColumn, "Data", each Text.Split(_, ", "), Columns)
in
ResultPower Query solution 4 for Hierarchical Project Indexing, proposed by Eric Laforce:
let
fxAddIndex = (t as table, cNames as list) =>
let
cn = cNames{0},
lastIdx = List.Last(List.Select(Table.ColumnNames(t), each Text.EndsWith(_, "_Index"))),
prefix = if (lastIdx = null) then "" else Table.Column(t, lastIdx){0} & ".",
values = List.Distinct(Table.Column(t, cn)),
Add_Idx = Table.AddColumn(
t,
cn & "_Index",
each prefix & Text.From(List.PositionOf(values, Record.Field(_, cn)) + 1)
)
in
if (List.Count(cNames) > 1) then
Table.Combine(Table.Group(Add_Idx, cn, {"G", each @fxAddIndex(_, List.Skip(cNames))})[G])
else
Add_Idx,
Source = Excel.CurrentWorkbook(){[Name = "tData221"]}[Content],
Result = fxAddIndex(Source, Table.ColumnNames(Source))
in
ResultPower Query solution 5 for Hierarchical Project Indexing, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddedIndex = Table.AddIndexColumn(Source, "IndexCol", 0, 1, Int64.Type),
AddRank = Table.AddRankColumn(AddedIndex, "Project_Index", {"Project"}, [RankKind = 1]),
Grouped = Table.Group(
AddRank,
{"Project"},
{
{
"Count",
each
let
a = Table.AddRankColumn(_, "Task_Index", {"Task"}, [RankKind = 1]),
b = Table.TransformColumns(
a,
{"Task_Index", each Text.From(a{_}[Project_Index]) & "." & Text.From(_)}
)
in
b
}
}
)[Count],
Expand = Table.Combine(Grouped),
Sorted = Table.Sort(Expand, {{"IndexCol", Order.Ascending}}),
Grouped2 = Table.Group(
Sorted,
"Task",
{
{
"Activities",
(a) =>
Table.AddColumn(
a,
"Activity_Index",
each Text.From([Task_Index])
& "."
& Text.From(List.PositionOf(List.Distinct(a[Activity]), [Activity]) + 1)
)
}
}
)[Activities],
Final = Table.RemoveColumns(Table.Sort(Table.Combine(Grouped2), "IndexCol"), "IndexCol")
in
FinalPower Query solution 6 for Hierarchical Project Indexing, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.Group(
Source,
{"Project"},
{
{
"Count",
each Table.AddIndexColumn(
Table.Group(
Table.Distinct(_[[Task], [Activity]]),
{"Task"},
{{"Count", each Table.AddIndexColumn(Table.Distinct(_[[Activity]]), "AI", 1)}}
),
"TI",
1
)
}
}
),
I1 = Table.AddIndexColumn(S1, "PI", 1, 1, Int64.Type),
S2 = Table.ExpandTableColumn(I1, "Count", {"Task", "Count", "TI"}, {"Task", "Count.1", "TI"}),
S3 = Table.ExpandTableColumn(S2, "Count.1", {"Activity", "AI"}, {"Activity", "AI"}),
S4 = Table.NestedJoin(
Source,
{"Project", "Task", "Activity"},
S3,
{"Project", "Task", "Activity"},
"Table1 (3)",
JoinKind.LeftOuter
),
S5 = Table.AddIndexColumn(S4, "Index", 1, 1, Int64.Type),
S6 = Table.ExpandTableColumn(S5, "Table1 (3)", {"AI", "TI", "PI"}, {"AI", "TI", "Project Index"}),
S7 = Table.AddColumn(S6, "Task Index", each Text.From([Project Index]) & "." & Text.From([TI])),
S8 = Table.AddColumn(S7, "Activity Index", each [Task Index] & "." & Text.From([AI])),
S9 = Table.RemoveColumns(S8, {"AI", "TI", "Index"})
in
S9Solving the challenge of Hierarchical Project Indexing with Excel
Excel solution 1 for Hierarchical Project Indexing, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:C20,
i,
MID(
DROP(
REDUCE(
N(
+TAKE(
z,
,
1
)
),
SEQUENCE(
COLUMNS(
z
)
),
LAMBDA(
p,
c,
LET(
a,
TAKE(
p,
,
-1
),
b,
INDEX(
z,
,
c
),
HSTACK(
p,
a&"."&MAP(
a,
b,
LAMBDA(
i,
j,
LET(
x,
FILTER(
b,
i=a
),
XMATCH(
j,
UNIQUE(
x
)
)
)
)
)
)
)
)
),
,
1
),
3,
9
),
VSTACK(
TOROW(
A1:C1&{"";"_Index"}
),
HSTACK(
z,
IFERROR(
--i,
i
)
)
)
)Excel solution 2 for Hierarchical Project Indexing, proposed by Julian Poeltl:
=LET(H,
A1:C1,
P,
A2:A20,
T,
B2:B20,
A,
C2:C20,
RC,
CODE(
RIGHT(
A
)
)-64,
TI,
--MAP(
T,
LAMBDA(
A,
TEXTJOIN(
",",
,
CODE(
MID(
A,
SEQUENCE(
2
),
1
)
)-64
)
)
),
HSTACK(VSTACK(
H,
HSTACK(
P,
T,
A
)
),
VSTACK(H&"_Index",
HSTACK(CODE(
P
)-64,
TI,
SUBSTITUTE(
TI,
",",
"."
)&"."&IF((RC>2)*(SCAN(0,
IFNA((TI<>DROP(
TI,
1
))*(RC<>DROP(
RC,
1
)),
0),
SUM)>0),
1,
RC)))))Excel solution 3 for Hierarchical Project Indexing, proposed by Oscar Mendez Roca Farell:
=LET(
s,
".",
p,
A2:A20,
F,
LAMBDA(
a,
b,
DROP(
REDUCE(
"",
UNIQUE(
a
),
LAMBDA(
y,
j,
LET(
f,
FILTER(
b,
a=j
),
VSTACK(
y,
XMATCH(
f,
UNIQUE(
f
)
)
)
)
)
),
1
)
),
x,
XMATCH(
p,
UNIQUE(
p
)
),
t,
F(
p,
B2:B20
),
c,
F(
B2:B20,
C2:C20
),
HSTACK(
A1:C20,
VSTACK(
HSTACK(
A1:C1&"_Index"
),
HSTACK(
x,
x&s&t,
x&s&t&s&c
)
)
)
)Excel solution 4 for Hierarchical Project Indexing, proposed by Eddy Wijaya:
=LET(
d,
A2:C20,
l_c,
UNIQUE(
DROP(
REDUCE(
0,
TAKE(
d,
,
2
),
LAMBDA(
a,
v,
VSTACK(
a,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
)
)
)
),
1
)
),
d_bCol,
CHOOSECOLS(
d,
2
),
f,
LAMBDA(
x,
XMATCH(
x,
l_c,
0
)
),
a,
f(
TAKE(
d,
,
1
)
),
b,
--(f(
LEFT(
d_bCol,
1
)
)&"."&f(
RIGHT(
d_bCol,
1
)
)),
c,
BYROW(
TAKE(
d,
,
-1
),
LAMBDA(
r,
XMATCH(
r,
UNIQUE(
FILTER(
TAKE(
d,
,
-1
),
d_bCol=OFFSET(
r,
,
-1
)
)
),
0
)
)
),
HSTACK(
d,
a,
b,
b&"."&c
))Excel solution 5 for Hierarchical Project Indexing, proposed by Nonbow Wu:
=LET(
da,
A2:C20,
tsk,
INDEX(
da,
,
2
),
act,
INDEX(
da,
,
3
),
b,
64,
ut,
UNIQUE(
tsk
),
t_ndx,
XLOOKUP(
tsk,
ut,
CODE(
LEFT(
ut
)
)-b&"."&CODE(
RIGHT(
ut
)
)-b,
,
0
),
a_ndx,
MAP(
act,
tsk,
LAMBDA(
a,
t,
MATCH(
a,
UNIQUE(
FILTER(
act,
tsk=t
)
),
0
)
)
),
VSTACK(
HSTACK(
A1:C1,
A1:C1&"_Index"
),
HSTACK(
da,
LEFT(
t_ndx
),
t_ndx,
t_ndx&"."&a_ndx
)
)
)Solving the challenge of Hierarchical Project Indexing with Python
Python solution 1 for Hierarchical Project Indexing, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_221.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=20)
test = pd.read_excel(path, usecols="E:J", nrows=20).rename(columns=lambda x: x.replace('.1', ''))
test["Task_Index"] = test["Task_Index"].astype(str)
input['Project_Index'] = (input['Project'].astype('category').cat.codes + 1).astype(np.int64)
input['Task_Index'] = (input['Project_Index'].astype(str) + "." +
input.groupby('Project')['Task']
.transform(lambda x: (x.astype('category').cat.codes + 1).astype(str)))
input['Activity_Index'] = (input['Task_Index'] + "." +
input.groupby(['Project', 'Task'])['Activity']
.transform(lambda x: (x.astype('category').cat.codes + 1).astype(str)))
print(input.equals(test)) # True
Python solution 2 for Hierarchical Project Indexing, proposed by Luke Jarych:
Python:
import pandas as pd
import xlwings as xw
import re
wb = xw.Book(r'PQ_Challenge_221.xlsx')
sh = wb.sheets[0]
table1 = sh.tables['Table1']
rng1 = sh.range(table1.range.address)
df = rng1.options(pd.DataFrame, header=True, index=False, numbers=float).value
df['Project_Index'] = df['Project'].rank(method='dense').astype(int).astype(str)
df['Task_Index'] = df.groupby('Project')['Task'].rank(method='dense').astype(int).astype(str)
df['Task_Index'] = df['Project_Index'] + '.' + df['Task_Index']
df['Activity_Index'] = df.groupby(['Project', 'Task'])['Activity'].rank(method='dense').astype(int).astype(str)
df['Activity_Index'] = df['Task_Index'] + '.' + df['Activity_Index']
& Solving the challenge of Hierarchical Project Indexing with Python in Excel
Python in Excel solution 1 for Hierarchical Project Indexing, proposed by Alejandro Campos:
def generate_indices(df):
df['Project_Index'] = df.groupby('Project').ngroup() + 1
df['Task_Index'] = df.groupby(['Project', 'Task']).ngroup() + 1
df['Task_Index'] = df['Project_Index'].astype(str) + ',' + df.groupby('Project')['Task'].transform(lambda x: pd.factorize(x)[0] + 1).astype(str)
df['Activity_Index'] = df.groupby(['Project', 'Task'])['Activity'].transform(lambda x: pd.factorize(x)[0] + 1)
df['Activity_Index'] = df['Project_Index'].astype(str) + '.' + df['Task_Index'].str.split(',').str[1] + '.' + df['Activity_Index'].astype(str)
final_df = df[['Project', 'Task', 'Activity', 'Project_Index', 'Task_Index', 'Activity_Index']]
return final_df
df = xl("A1:C20", headers=True)
final_df = generate_indices(df)
final_df
Python in Excel solution 2 for Hierarchical Project Indexing, proposed by Ümit Barış Köse, MSc:
df = xl("A1:C20", headers=True)
project_index = df.groupby('Project').ngroup() + 1
task_index = df.groupby(['Project', 'Task']).ngroup() + 1
activity_index = df.groupby(['Project', 'Task', 'Activity']).ngroup() + 1
df['Project_Index'] = project_index
df['Task_Index'] = df.groupby('Project')['Task'].transform(lambda x: pd.factorize(x)[0] + 1)
df['Activity_Index'] = df.groupby(['Project', 'Task'])['Activity'].transform(lambda x: pd.factorize(x)[0] + 1)
df['Task_Index'] = df['Project_Index'].astype(str) + ';' + df['Task_Index'].astype(str)
df['Activity_Index'] = df['Project_Index'].astype(str) + '.' + df['Task_Index'].str.split(';').str[1] + '.' + df['Activity_Index'].astype(str)
final_df = df[['Project', 'Task', 'Activity', 'Project_Index', 'Task_Index', 'Activity_Index']]
final_df.columns = ['Project', 'Task', 'Activity', 'Project_Index', 'Task_Index', 'Activity_Index']
final_df
Solving the challenge of Hierarchical Project Indexing with R
R solution 1 for Hierarchical Project Indexing, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_221.xlsx"
input = read_excel(path, range = "A1:C20")
test = read_excel(path, range = "E1:J20")
result = input %>%
mutate(Project_Index = as.numeric(as.factor(Project))) %>%
mutate(Task_Index = as.numeric(paste0(Project_Index,".",as.numeric(as.factor(Task)))) , .by = Project) %>%
mutate(Activity_Index = paste0(Task_Index,".", as.numeric(as.factor(Activity))), .by = c(Project, Task))
all.equal(result, test)
# [1] TRUE
&&