For each group, generate the index. For same employee in a group, Index will remain same.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 185
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Employee Group Index with Power Query
Power Query solution 1 for Generate Employee Group Index, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Index",
each List.PositionOf(
List.Distinct(Table.SelectRows(Source, (t) => t[Group] = [Group])[Emp]),
[Emp]
)
+ 1
)
in
AnsPower Query solution 2 for Generate Employee Group Index, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.AddColumn(
Source,
"Index",
each Table.PositionOf(
Table.SelectRows(Table.Distinct(Source), (r) => r[#"Group "] = [#"Group "]),
_
)
+ 1
)
in
SPower Query solution 3 for Generate Employee Group Index, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.AddColumn(
Source,
"Index",
each Table.PositionOf(Table.Distinct(Source), _)
- Table.PositionOf(Table.Distinct(Source)[[#"Group "]], [#"Group " = [#"Group "]])
+ 1
)
in
SPower Query solution 4 for Generate Employee Group Index, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Idx = Table.AddColumn(
Source,
"Index",
each List.PositionOf(
List.Distinct(Table.SelectRows(Source, (x) => x[#"Group "] = [#"Group "])[Emp]),
[Emp]
)
+ 1
)
in
IdxPower Query solution 5 for Generate Employee Group Index, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Group ",
{
"All",
each Table.AddIndexColumn(
Table.Group(_, "Emp", {"Group", (f) => f[#"Group "]}),
"Index",
1,
1
)
}
)[[All]],
Expand1 = Table.ExpandTableColumn(
Group,
"All",
{"Emp", "Group", "Index"},
{"Emp", "Group", "Index"}
),
Expand2 = Table.ExpandListColumn(Expand1, "Group"),
Return = Table.ReorderColumns(Expand2, {"Group", "Emp", "Index"})
in
ReturnPower Query solution 6 for Generate Employee Group Index, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Group "},
{
{
"A",
each
let
a = _,
b = Table.Group(a, {"Emp"}, {{"B", each [Emp]}}),
c = Table.AddIndexColumn(b, "Idx", 1, 1)
in
c
}
}
),
ExpA = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0})),
Sol = Table.RemoveColumns(Table.ExpandListColumn(ExpA, "B"), "B")
in
SolPower Query solution 7 for Generate Employee Group Index, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(
Fonte,
{"Group "},
{"tab", each Table.AddIndexColumn(Table.Group(_, {"Emp"}, {"group", each _}), "Ind", 1, 1)}
),
exp = Table.ExpandTableColumn(gp, "tab", {"group", "Ind"}),
res = Table.ExpandTableColumn(exp, "group", {"Emp"})
in
resPower Query solution 8 for Generate Employee Group Index, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DistG = List.Distinct(Source[#"Group "]),
Part = Table.FromList(
List.Transform(
Table.Partition(Source, "Group ", List.Count(DistG), each List.PositionOf(DistG, _)),
each Table.Distinct(_)
),
Splitter.SplitByNothing(),
null,
null
),
AddIdx = Table.RemoveColumns(
Table.AddColumn(Part, "IdxCol", each Table.AddIndexColumn([Column1], "Idx", 1, 1)),
"Column1"
),
Expand = Table.ExpandTableColumn(
AddIdx,
"IdxCol",
{"Group ", "Emp", "Idx"},
{"Groupx ", "Empx", "Index"}
),
Join = Table.RemoveColumns(
Table.Join(Source, {"Group ", "Emp"}, Expand, {"Groupx ", "Empx"}, JoinKind.LeftOuter),
{"Groupx ", "Empx"}
)
in
JoinPower Query solution 9 for Generate Employee Group Index, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.Distinct(S),
b = Table.Group(a, {"Group "}, {"G", each Table.AddIndexColumn(_, "I", 1)}),
c = Table.Combine(b[[G]][G]),
Sol = Table.AddColumn(S, "Index", each c[I]{List.PositionOf(c[Emp], [Emp])})
in
SolPower Query solution 10 for Generate Employee Group Index, proposed by Albert Cid Cañigueral:
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ndA = Table.Group(Origen,{"Group","Emp"},{"Tablas", each _}),
ndB = Table.Group(ndA,{"Group"},{"T", each Table.AddIndexColumn(_, "Index",1)}),
ndC = Table.ExpandTableColumn(ndB, "T", {"Tablas", "Index"}, { "Tablas", "Index"}),
ndD = Table.ExpandTableColumn(ndC, "Tablas", {"Emp"}, {"Emp"})
in
ndD
Show translation
Show translation of this commentPower Query solution 11 for Generate Employee Group Index, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.Distinct(S),
B = Table.Group(
A,
{"Group "},
{{"Tbl", each _, type table [#"Group " = nullable text, Emp = nullable text]}}
),
C = Table.AddColumn(B, "Tbl2", each Table.AddIndexColumn([Tbl], "Index", 1, 1)),
D = Table.SelectColumns(C, {"Tbl2"}),
S2 = Table.ExpandTableColumn(D, "Tbl2", {"Group ", "Emp", "Index"}, {"Group ", "Emp", "Index"}),
E = Table.NestedJoin(S, {"Group ", "Emp"}, S2, {"Group ", "Emp"}, "T"),
Sol = Table.ExpandTableColumn(E, "T", {"Index"}, {"Index"})
in
SolPower Query solution 12 for Generate Employee Group Index, proposed by Peter Tholstrup:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
group = Table.Group(Source, {"Group "}, {"Temp", each _}),
get_index = (t) =>
[i = each List.PositionOf(List.Distinct(t[Emp]), [Emp]) + 1, r = Table.AddColumn(t, "Index", i)][
r
],
index = Table.TransformColumns(group, {"Temp", get_index}),
result = Table.Combine(index[Temp])
in
resultPower Query solution 13 for Generate Employee Group Index, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.ExpandTableColumn(
Table.ExpandTableColumn(
Table.TransformColumns(
Table.Group(
Table.Group(Source, {"Group ", "Emp"}, {{"Count", each _}}),
{"Group "},
{{"Count", each _}}
),
{"Count", each Table.AddIndexColumn(_, "Index", 1, 1)}
),
"Count",
{"Count", "Index"},
{"x", "Index"}
),
"x",
{"Emp"},
{"Emp"}
)
in
ResultPower Query solution 14 for Generate Employee Group Index, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupedRows1= Table.Group(Source, {"Group ", "Emp"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
GroupedRows2 = Table.Group(GroupedRows1, {"Group "}, {{"Count", each
let a =_,
b = Table.AddIndexColumn(a, "Index", 1, 1),
c = Table.AddColumn(b, "Sequence", each {1..[Count]}),
d = Table.ExpandListColumn(c, "Sequence")
in d
}}),
ExpandedCount = Table.ExpandTableColumn(GroupedRows2, "Count", {"Emp", "Index"}, {"Emp", "Index"})
in
ExpandedCount
Power Query solution - in easy steps :)
Power Query solution 15 for Generate Employee Group Index, proposed by Venkata Rajesh:
let
Source = Data,
Output = Table.AddColumn(
Source,
"Index",
each [
x = [Group],
y = Table.SelectRows(Source, each [Group] = x),
z = Table.PositionOf(Table.Distinct(y), _) + 1
][z]
)
in
OutputSolving the challenge of Generate Employee Group Index with Excel
Excel solution 1 for Generate Employee Group Index, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
A2:A13,
e,
B2:B13,
HSTACK(
g,
e,
MAP(
g,
e,
LAMBDA(
a,
b,
XMATCH(
b,
UNIQUE(
FILTER(
e,
g=a
)
)
)
)
)
)
)Excel solution 2 for Generate Employee Group Index, proposed by Rick Rothstein:
=LET(
a,
A1:A13,
b,
B1:B13,
DROP(
SCAN(
0,
SEQUENCE(
COUNTA(
b
)
),
LAMBDA(
c,
x,
IF(
INDEX(
a,
x
)<>INDEX(
a,
x+1
),
1,
IF(
INDEX(
b,
x
)<>INDEX(
b,
x+1
),
c+1,
c
)
)
)
),
-1
)
)Excel solution 3 for Generate Employee Group Index, proposed by محمد حلمي:
=SCAN(,
IF(
A2:A13=A1:A12,
B2:B13<>B1:B12,
1
),
LAMBDA(a,
v,
(v>1)*a+v))Excel solution 4 for Generate Employee Group Index, proposed by Julian Poeltl:
=LET(
T,
A1:B13,
TT,
DROP(
T,
1
),
G,
TAKE(
TT,
,
1
),
E,
TAKE(
TT,
,
-1
),
UE,
UNIQUE(
E
),
UG,
XLOOKUP(
UE,
E,
G
),
I,
XMATCH(
UE,
UE
)-XMATCH(
UG,
UG
)+1,
HSTACK(
T,
VSTACK(
"Index",
XLOOKUP(
E,
UE,
I
)
)
)
)Excel solution 5 for Generate Employee Group Index, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(A2:A13, LAMBDA(a, ROWS(UNIQUE(FILTER(TAKE(A2:B13, 1, -1):a, TAKE(A2:B13, 1, 1):a = a)))))Excel solution 6 for Generate Employee Group Index, proposed by Oscar Mendez Roca Farell:
=HSTACK(A2:B13, MAP(B2:B13, LAMBDA(b, LET(r, A2:b, ROWS(UNIQUE(TAKE(r, -COUNTIF(r, @+TAKE(r, -1)))))))))Excel solution 7 for Generate Employee Group Index, proposed by Duy Tùng:
=LET(
a,
A2:A13,
HSTACK(
A1:B13,
REDUCE(
"Index",
UNIQUE(
a
),
LAMBDA(
x,
y,
LET(
b,
FILTER(
B2:B13,
a=y
),
VSTACK(
x,
SCAN(
0,
b<>VSTACK(
0,
DROP(
b,
-1
)
),
SUM
)
)
)
)
)
)
)Excel solution 8 for Generate Employee Group Index, proposed by Sunny Baggu:
=REDUCE(
HSTACK(
A1:B1,
"Index"
),
UNIQUE(
A2:A13
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_a,
FILTER(
A2:B13,
A2:A13 = y
),
_b,
TAKE(
_a,
,
-1
),
_c,
VSTACK(
1,
N(
DROP(
_b,
1
) = DROP(
_b,
-1
)
)
),
HSTACK(
_a,
SCAN(
1,
_c,
LAMBDA(
a,
v,
IF(
v = 1,
a,
a + 1
)
)
)
)
)
)
)
)Excel solution 9 for Generate Employee Group Index, proposed by Asheesh Pahwa:
=LET(g,A2:A13,e,B2:B13,u,UNIQUE(g),REDUCE(D1:F1,u,LAMBDA(x,y,
VSTACK(x,LET(f,FILTER(e,g=y),un,UNIQUE(f),s,SEQUENCE(ROWS(un)),
IFNA(HSTACK(y,f,XLOOKUP(f,un,s)),y))))))Excel solution 10 for Generate Employee Group Index, proposed by Albert Cid Cañigueral:
=APILARV(
A1:B1;
APILARH(
A2:A13;
SCAN(
0;
B2:B13;
LAMBDA(
a;
e;
SI(
DESREF(
e;
0;
-1
)<>DESREF(
e;
-1;
-1
);
1;
SI(
DESREF(
e;
-1;
0
)<>e;
a + 1;
a
)
)
)
)
)
)Excel solution 11 for Generate Employee Group Index, proposed by Mey Tithveasna:
=HSTACK(
A2:A13,
B2:B13,
MAP(
A2:A13,
B2:B13,
LAMBDA(
a,
b,
MATCH(
b,
UNIQUE(
FILTER(
B2:B13,
a=A2:A13
)
),
0
)
)
)
)Excel solution 12 for Generate Employee Group Index, proposed by Anup Kumar:
=LET(
gre,
A2:A13&B2:B13,
VSTACK(
1,
--RIGHT(
SCAN(
CONCAT(
TAKE(
gre,
1
),
1
),
DROP(
gre,
1
),
LAMBDA(
x,
y,
IFS(
LEFT(
y,
1
)<>LEFT(
x,
1
),
CONCAT(
y,
1
),
MID(
y,
2,
LEN(
y
)-1
)<>MID(
x,
2,
LEN(
x
)-2
),
CONCAT(
y,
RIGHT(
x,
1
)+1
),
TRUE,
CONCAT(
y,
RIGHT(
x,
1
)
)
)
)
)
,
1
)
)
)Excel solution 13 for Generate Employee Group Index, proposed by Erik Oehm:
=HSTACK(A2:B13,VSTACK(1,DROP(SCAN(1,BYROW(A2:B13<>DROP(A2:B13,1),LAMBDA(x,IMSUM(({0,1}-x)&{"","i"}))),LAMBDA(s,x,s*(1+IMREAL(x))-IMAGINARY(x)+1)),-1)))Solving the challenge of Generate Employee Group Index with Python
Python solution 1 for Generate Employee Group Index, proposed by Konrad Gryczan, PhD:
Only one of those in R - factorization approach
import pandas as pd
input = pd.read_excel("PQ_Challenge_185.xlsx", sheet_name="Sheet1", usecols="A:B")
test = pd.read_excel("PQ_Challenge_185.xlsx", sheet_name="Sheet1", usecols="D:F")
test.columns = test.columns.str.replace('.1', '')
input['Index'] = input.groupby('Group ')['Emp'].transform(lambda x: pd.factorize(x)[0]+1)
print(input.equals(test)) # True
Solving the challenge of Generate Employee Group Index with Python in Excel
Python in Excel solution 1 for Generate Employee Group Index, proposed by Alejandro Campos:
df = xl("A1:B13", headers=True).assign(
Index=lambda d: d.groupby('Group')['Emp'].
transform(lambda x: pd.factorize(x)[0] + 1))
df
Python in Excel solution 2 for Generate Employee Group Index, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_185.xlsx'
df = pd.read_excel(file_path, usecols='A:B')
# Perform data wrangling
df1 = df.drop_duplicates().copy()
df1['Index'] = df1.groupby('Group').cumcount() + 1
df = df1.merge(df)
df
Solving the challenge of Generate Employee Group Index with Excel VBA
Excel VBA solution 1 for Generate Employee Group Index, proposed by Rushikesh K.:
Option Explicit
Sub GenerateIndexNumbers()
Dim ws As Worksheet
Dim lastRow As Long
Dim currentGroup As String
Dim currentIndex As Long
Dim i As Long
Dim employeeDict As Object
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set employeeDict = CreateObject("Scripting.Dictionary")
currentGroup = ws.Cells(2, 1).Value
currentIndex = 1
employeeDict.Add ws.Cells(2, 2).Value, currentIndex
ws.Cells(2, 3).Value = currentIndex
For i = 3 To lastRow
If ws.Cells(i, 1).Value <> currentGroup Then
currentGroup = ws.Cells(i, 1).Value
currentIndex = 1
Set employeeDict = CreateObject("Scripting.Dictionary")
Else
If Not employeeDict.exists(ws.Cells(i, 2).Value) Then
currentIndex = currentIndex + 1
employeeDict.Add ws.Cells(i, 2).Value, currentIndex
End If
End If
ws.Cells(i, 3).Value = employeeDict(ws.Cells(i, 2).Value)
Next i
End Sub
&&