Get names in a sorted order under subject columns. Repeat them over Mon to Fri. Unused ones should carry over to Backup days.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 245
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Get names in a sorted with Power Query
Power Query solution 1 for Get names in a sorted, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
_ = Table.Group(
Table.FromRows(
List.Sort(
List.TransformMany(Table.ToRows(Source), each Text.Split(_{1}, ", "), (i, _) => {i{0}, _}),
{each _{1}, each _{0}}
),
{"N", "S"}
),
"S",
{"A", each List.Repeat([N], Number.RoundUp(5 / List.Count([N])))}
),
S = Table.FromColumns(
{
List.Transform(
{0 .. List.Max(List.Transform([A], List.Count)) - 1},
each {"Mon", "Tue", "Wed", "Thu", "Fri"}{_}? ?? "Backup" & Text.From(_ - 4)
)
}
& [A],
{"Days"} & [S]
)
in
SPower Query solution 2 for Get names in a sorted, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.TransformColumns(Source, {{"Subjects", each Text.Split(_, ", ")}}),
Exp = Table.ExpandListColumn(Split, "Subjects"),
Group = Table.Sort(
Table.Group(
Exp,
{"Subjects"},
{
{
"A",
each
let
a = List.Sort([Names]),
b = List.Count(a),
c = List.Repeat(a, Number.RoundUp(5 / b))
in
c
}
}
),
"Subjects"
),
Max = List.Max(List.Transform(Group[A], List.Count)),
Days = {"Mon", "Tue", "Wed", "Thu", "Fri"}
& List.Transform({1 .. Max - 5}, each "Backup" & Text.From(_)),
Sol = Table.FromColumns({Days} & Group[A], {"Days"} & Group[Subjects])
in
SolPower Query solution 3 for Get names in a sorted, proposed by Luan Rodrigues:
let
days = {"Mon", "Tue", "Wed", "Thu", "Fri"},
sort = Table.Sort(Tabela1, {"Names"}),
spt = Table.TransformColumns(sort, {"Subjects", each Text.Split(_, ", ")}),
exp = Table.ExpandListColumn(spt, "Subjects"),
grp = Table.Group(
exp,
{"Subjects"},
{
{
"tab",
each [
a = Table.Repeat(_, Number.RoundUp(List.Count(days) / List.Count(_[Names]))),
b = days
& List.RemoveLastN(
List.Transform({1 .. List.Count(a[Names])}, each "Backup" & Text.From(_)),
List.Count(days)
),
c = Table.FromColumns({b} & Table.ToColumns(a))
]
}
}
),
cls = Table.Sort(grp, {"Subjects"}),
cmb = Table.Combine(List.Transform(cls[tab], (x) => x[c])),
pvt = Table.Pivot(cmb, List.Distinct(cmb[Column3]), "Column3", "Column2"),
srt = Table.Sort(pvt, {each List.PositionOf(List.Distinct(cmb[Column1]), [Column1])}),
ren = Table.RenameColumns(srt, {{"Column1", "Days"}})
in
renPower Query solution 4 for Get names in a sorted, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData245"]}[Content],
Transform = Table.FromRows(
List.Accumulate(
Table.ToRows(Source),
{},
(s, c) => s & List.Transform(Text.Split(c{1}, ", "), each {c{0}, _})
),
{"Name", "Subject"}
),
Group = Table.Group(
Transform,
"Subject",
{"G", each List.Repeat(List.Sort(_[Name]), Number.RoundUp(5 / List.Count(_[Name])))}
),
Sort = Table.Sort(Group, "Subject"),
NbBackup = List.Max(List.Transform(Sort[G], List.Count)) - 5,
Backups = List.Transform({1 .. NbBackup}, each "Backup" & Text.From(_)),
ToTable = Table.FromColumns(
{{"Mond", "Tue", "Wed", "Thu", "Fri"} & Backups} & Sort[G],
{"Days"} & Sort[Subject]
)
in
ToTablePower Query solution 5 for Get names in a sorted, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToList = Table.TransformColumns(Source, {{"Subjects", each Text.Split(_, ", ")}}),
Expand = Table.ExpandListColumn(ToList, "Subjects"),
Group = Table.Sort(
Table.Group(Expand, {"Subjects"}, {"tbl", each Table.Sort(_, "Names")[Names]}),
"Subjects"
),
Names = List.Transform(Group[tbl], each List.Repeat(_, Number.RoundUp(5 / List.Count(_)))),
Backups = List.Max(List.Transform(Names, each List.Count(_))) - 5,
Days = {
{"Mon", "Tue", "Wed", "Thu", "Fri"}
& List.Transform({1 .. Backups}, each "Backup" & Text.From(_))
},
Cols = Days & Names,
ColNames = {"Days"} & Group[Subjects],
Res = Table.FromColumns(Cols, ColNames)
in
ResPower Query solution 6 for Get names in a sorted, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DL = {"Mon", "Tue", "Wed", "Thu", "Fri"},
A = Table.AddColumn(S, "SL", each Text.Split([Subjects], ", ")),
B = Table.SelectColumns(A, {"Names", "SL"}),
C = Table.ExpandListColumn(B, "SL"),
D = Table.Group(C, {"SL"}, {{"C", each Table.RowCount(_)}, {"T", each _}}),
E = Table.AddColumn(
D,
"Name(R)",
each List.Repeat([T][Names], Number.RoundUp(List.Count(DL) / [C]))
),
F = Table.AddColumn(E, "LC", each List.Count([#"Name(R)"])),
G = Table.Sort(F, {{"SL", Order.Ascending}}),
DLN = DL & List.Transform({1 .. List.Max(G[LC]) - List.Count(DL)}, each "Backup" & Text.From(_)),
H = Table.FromColumns(G[#"Name(R)"], G[SL]),
I = Table.AddColumn(H, "D", each DLN),
K = Table.AddIndexColumn(I, "I", 0, 1),
L = Table.AddColumn(K, "Days", each [D]{[I]}),
M = Table.ReorderColumns(L, {"Days", "Arts", "English", "Maths", "D", "I"}),
N = Table.SelectColumns(M, {"Days", "Arts", "English", "Maths"})
in
NSolving the challenge of Get names in a sorted with Excel
Excel solution 1 for Get names in a sorted, proposed by Bo Rydobon 🇹🇭:
=LET(
s,
TEXTSPLIT(
TEXTJOIN(
0,
0,
B2:B6
),
", ",
0
),
g,
TRANSPOSE(
TEXTSPLIT(
CONCAT(
GROUPBY(
TOCOL(
s,
3
)&" ",
TOCOL(
IF(
s>0,
A2:A6
),
3
),
LAMBDA(
x,
REPT(
CONCAT(
SORT(
x
)&" "
),
ROUNDUP(
5/ROWS(
x
),
)
)&0
),
,
0
)
),
" ",
0,
1,
,
""
)
),
n,
SEQUENCE(
ROWS(
g
)
),
HSTACK(
IFS(
n=1,
"Day",
n>6,
"Backup"&n-6,
1,
TEXT(
n,
"ddd"
)
),
g
)
)Excel solution 2 for Get names in a sorted, proposed by Julian Poeltl:
=LET(N,
A2:A6,
S,
B2:B6,
H,
TOROW(
SORT(
UNIQUE(
TEXTSPLIT(
TEXTJOIN(
", ",
,
S
),
,
", "
)
)
)
),
NBS,
MAP(
H,
LAMBDA(
A,
TEXTJOIN(
",",
,
SORT(
FILTER(
N,
ISNUMBER(
SEARCH(
A,
S
)
)
)
)
)
)
),
T,
IFNA(DROP(REDUCE(0,
MAP(NBS,
LAMBDA(A,
REPT(A&",",
ROUNDUP(6/(LEN(
A
)-LEN(
SUBSTITUTE(
A,
",",
""
)
)+1),
0)))),
LAMBDA(
A,
B,
HSTACK(
A,
TEXTSPLIT(
B,
,
","
)
)
)),
-1,
1),
""),
HSTACK(
VSTACK(
"Days",
TEXT(
SEQUENCE(
5,
,
2
),
"TTT"
),
"Backup"&SEQUENCE(
3
)
),
VSTACK(
H,
T
)
))Excel solution 3 for Get names in a sorted, proposed by Hussein SATOUR:
=LET(
W,
ARRAYTOTEXT,
P,
TEXTSPLIT,
S,
SEQUENCE,
V,
VSTACK,
n,
A2:A6,
u,
B2:B6,
b,
CONCAT(
BYROW(
SORT(
UNIQUE(
P(
W(
u
),
,
", "
)
)
),
LAMBDA(
z,
z&", "&LET(
a,
SORT(
FILTER(
n,
IFERROR(
FIND(
z,
u
),
0
)
)
),
W(
REDUCE(
a,
S(
ROUNDDOWN(
5/COUNTA(
a
),
0
)
),
LAMBDA(
x,
y,
V(
x,
a
)
)
)
)&"/"
)
)
)
),
c,
TRANSPOSE(
P(
b,
", ",
"/",
1,
,
""
)
),
HSTACK(
V(
"Days",
TEXT(
S(
5,
,
2
),
"ddd"
),
"Backup"&S(
ROWS(
c
)-6
)
),
c
)
)Excel solution 4 for Get names in a sorted, proposed by Oscar Mendez Roca Farell:
=LET(
t,
TEXTSPLIT(
CONCAT(
B2:B6&", "
),
,
", ",
1
),
s,
SEQUENCE(
1+MAX(
6,
2*MOD(
DROP(
GROUPBY(
t,
t,
ROWS,
,
0
),
,
1
),
5
)
)
)-1,
REDUCE(
IFS(
s>5,
"Backup"&MOD(
s,
5
),
s=0,
"Days",
1,
TEXT(
s+1,
"b1ddd"
)
),
SORT(
UNIQUE(
t
)
),
LAMBDA(
i,
x,
LET(
m,
TOCOL(
IFS(
FIND(
x,
B2:B6
),
A2:A6
),
2
),
r,
ROWS(
m
),
IFNA(
HSTACK(
i,
VSTACK(
x,
TOCOL(
REPT(
SORT(
m
),
SEQUENCE(
,
IF(
MOD(
r-1,
4
),
1+5/r,
1
)
)^0
),
,
1
)
)
),
""
)
)
)
)
)Excel solution 5 for Get names in a sorted, proposed by Sunny Baggu:
=LET(
_s,
TOROW(
SORT(
UNIQUE(
TEXTSPLIT(
ARRAYTOTEXT(
B2:B8
),
,
{",",
", "},
1
)
)
)
),
_a,
DROP(
REDUCE(
"",
_s,
LAMBDA(
a,
v,
HSTACK(
a,
SORT(
TOCOL(
IF(
SEARCH(
v,
B2:B8
),
A2:A8,
x
),
3
)
)
)
)
),
,
1
),
_r,
BYCOL(
SEARCH(
_s,
B2:B8
),
LAMBDA(
a,
ROWS(
TOCOL(
a,
3
)
)
)
),
_rm,
MAX(
_r
),
_wd,
5,
_rt,
IF(
_rm < 7,
2 * _rm,
5
),
_rp,
ROUNDUP(
_wd / _r,
0
),
_b,
SEQUENCE(
_rt
) - _wd,
_d,
IF(
_b > 0,
"Backup" & _b,
{"Mon"; "Tue"; "Wed"; "Thu"; "Fri"}
),
_e,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
_a
)
),
LAMBDA(
x,
y,
HSTACK(
x,
TOCOL(
IF(
SEQUENCE(
,
INDEX(
&_rp,
1,
y
)
),
TOCOL(
INDEX(
_a,
,
y
),
3
)
),
,
1
)
)
)
),
,
1
),
""
),
_dr,
TAKE(
_d,
ROWS(
_e
)
),
_fr,
VSTACK(
HSTACK(
"Days",
_s
),
HSTACK(
_dr,
_e
)
),
_fr
)Excel solution 6 for Get names in a sorted, proposed by Md. Zohurul Islam:
=LET(
nam,
A2:A6,
sub,
B2:B6,
dys,
TEXT(
SEQUENCE(
5,
,
2
),
"ddd"
),
a,
MAP(
nam,
sub,
LAMBDA(
x,
y,
REPT(
x&",",
COUNTA(
TEXTSPLIT(
y,
", "
)
)
)
)
),
b,
DROP(
TEXTSPLIT(
CONCAT(
a
),
,
","
),
-1
),
p,
DROP(
REDUCE(
"",
sub,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
,
", "
)
)
)
),
1
),
u,
TOROW(
SORT(
UNIQUE(
p
)
)
),
v,
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
LET(
f,
SORT(
FILTER(
b,
p=y
)
),
g,
COUNTA(
f
),
h,
ROUNDUP(
5/g,
0
),
j,
IFS(
h=5,
VSTACK(
f,
f,
f,
f,
f
),
h=4,
VSTACK(
f,
f,
f,
f
),
h=3,
VSTACK(
f,
f,
f
),
h=2,
VSTACK(
f,
f
),
h=1,
f
),
k,
HSTACK(
x,
j
),
k
)
)
),
,
1
),
w,
IFNA(
VSTACK(
u,
v
),
""
),
q,
ROWS(
v
)-5,
r,
"Backup" &SEQUENCE(
q
),
s,
VSTACK(
"Days",
dys,
r
),
z,
HSTACK(
s,
w
),
z
)Excel solution 7 for Get names in a sorted, proposed by Md Ismail Hosen:
=LAMBDA(
NamesVsSubjectMap,
LET(
_Days,
VSTACK(
"Mon",
"Tue",
"Wed",
"Thu",
"Fri"
),
_DaysCount,
ROWS(
_Days
),
_SubjectVsNameMap,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
NamesVsSubjectMap
)
),
LAMBDA(
a,
c,
LET(
Subjects,
TEXTSPLIT(
INDEX(
NamesVsSubjectMap,
c,
2
),
" ",
", "
),
Result,
VSTACK(
a,
EXPAND(
Subjects,
,
2,
INDEX(
NamesVsSubjectMap,
c,
1
)
)
),
Result
)
)
),
1
),
_SubjectExpanded,
TAKE(
_SubjectVsNameMap,
,
1
),
_StudentNameExpanded,
TAKE(
_SubjectVsNameMap,
,
-1
),
_SubjectByStudentCount,
GROUPBY(
_SubjectExpanded,
_StudentNameExpanded,
ROWS,
0,
0
),
_RelevantSubjectStudentNames,
BYROW(
_SubjectByStudentCount,
LAMBDA(
row,
REPT(
CONCAT(
SORT(
FILTER(
_StudentNameExpanded,
_SubjectExpanded = INDEX(
row,
1,
1
)
)
) & ", "
),
ROUNDUP(
_DaysCount / INDEX(
row,
1,
2
),
0
)
)
)
),
_StudentsNameExpanded,
IFNA(
DROP(
REDUCE(
"",
_RelevantSubjectStudentNames,
LAMBDA(
a,
c,
HSTACK(
a,
TEXTSPLIT(
c,
" ",
", ",
TRUE
)
)
)
),
,
1
),
""
),
_Headers,
VSTACK(
_Days,
"Backup" & SEQUENCE(
ROWS(
_StudentsNameExpanded
) - _DaysCount
)
),
_Result,
VSTACK(
HSTACK(
"Days",
TRANSPOSE(
TAKE(
_SubjectByStudentCount,
,
1
)
)
),
HSTACK(
_Headers,
_StudentsNameExpanded
)
),
_Result
)
)(A2:B6)Excel solution 8 for Get names in a sorted, proposed by Gabriel Pugliese:
=LET(
d,
A2:B6,
sn,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
d
)
),
LAMBDA(
a,
v,
VSTACK(
a,
REGEXEXTRACT(
INDEX(
TAKE(
d,
,
-1
),
v,
),
"w+",
1
)&"|"&INDEX(
TAKE(
d,
,
1
),
v,
)
)
)
),
1
),
us,
SORT(
UNIQUE(
TOCOL(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
d
)
),
LAMBDA(
a,
v,
VSTACK(
a,
REGEXEXTRACT(
INDEX(
TAKE(
d,
,
-1
),
v,
),
"w+",
1
)
)
)
),
1
),
3
)
)
),
nn,
DROP(
REDUCE(
"",
us,
LAMBDA(
a,
v,
HSTACK(
a,
SORT(
TOCOL(
IFS(
REGEXTEST(
sn,
v
),
TEXTAFTER(
sn,
"|"
)
),
3
)
)
)
)
),
,
1
),
numn,
BYCOL(
nn,
LAMBDA(
c,
COUNTA(
TOCOL(
c,
3
)
)
)
),
rep,
ROUNDUP(
5/numn,
0
),
gr,
REDUCE(
"",
SEQUENCE(
COUNT(
numn
)
),
LAMBDA(
a,
i,
HSTACK(
a,
DROP(
REDUCE(
"",
SEQUENCE(
SUM(
INDEX(
rep,
,
i
)
)
),
LAMBDA(
a,
v,
VSTACK(
a,
TOCOL(
INDEX(
nn,
,
i
),
3
)
)
)
),
1
)
)
)
),
l,
TOCOL(
HSTACK(
TEXT(
SEQUENCE(
,
5,
2
),
"ddd"
),
"backup"&SEQUENCE(
,
ROWS(
gr
)-5
)
)
),
rst,
IFNA(
VSTACK(
HSTACK(
"Days",
TOROW(
us
)
),
HSTACK(
l,
DROP(
gr,
,
1
)
)
),
""
),
rst
)Solving the challenge of Get names in a sorted with Python
Python solution 1 for Get names in a sorted, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_245.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=6)
test = pd.read_excel(path, usecols="D:G", nrows=9).fillna('')
input = input.assign(Subjects=input['Subjects'].str.split(', ')).explode('Subjects')
r1 = input.groupby('Subjects')['Names'].apply(lambda x: sorted(x.tolist())).reset_index()
weekdays = ["Mon", "Tue", "Wed", "Thu", "Fri"]
weekday_n = len(weekdays)
subjects_list = r1.set_index('Subjects')['Names'].apply(lambda x: np.array(x)).to_dict()
longest_subject = max(map(len, subjects_list.values()))
first_col = [""] * (longest_subject * -(-weekday_n // longest_subject))
subjects = {k: np.tile(v, -(-weekday_n // len(v))) for k, v in subjects_list.items()}
df = pd.DataFrame({
'Days': weekdays + [f"Backup{i}" for i in range(1, len(first_col) - weekday_n + 1)],
**{subject: np.concatenate([names, [''] * (len(first_col) - len(names))]) for subject, names in subjects.items()}
})
print(df.equals(test)) # True
Solving the challenge of Get names in a sorted with Python in Excel
Python in Excel solution 1 for Get names in a sorted, proposed by Alejandro Campos:
data_input = xl("A1:B6", headers=True).assign(Subjects=lambda df: df['Subjects'].str.split(', ')).explode('Subjects')
subjects_dict = data_input.groupby('Subjects')['Names'].apply(lambda x: np.array(sorted(x))).to_dict()
max_len = max(map(len, subjects_dict.values()))
weekdays = ["Mon", "Tue", "Wed", "Thu", "Fri"]
first_col_len = max_len * -(-len(weekdays) // max_len)
subjects_repeated = {subj: np.tile(names, -(-len(weekdays) // len(names))) for subj, names in subjects_dict.items()}
first_col = weekdays + [f"Backup{i}" for i in range(1, first_col_len - len(weekdays) + 1)]
schedule_df = pd.DataFrame({
'Days': first_col,
**{subj: np.pad(names, (0, len(first_col) - len(names)), constant_values='') for subj, names in subjects_repeated.items()}
})
schedule_df
Solving the challenge of Get names in a sorted with R
R solution 1 for Get names in a sorted, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_245.xlsx"
input = read_excel(path, range = "A1:B6")
test = read_excel(path, range = "D1:G9")
r1 = input %>%
separate_rows(Subjects, sep = ", ") %>%
group_by(Subjects) %>%
summarise(Names = list(sort(Names)), .groups = 'drop')
weekdays = c("Mon", "Tue", "Wed", "Thu", "Fri")
weekday_n = length(weekdays)
subjects_list = r1 %>%
summarise(Names = list(unlist(Names)), .by = Subjects) %>%
deframe()
longest_subject = max(map_int(r1$Names, length))
first_col = rep("", longest_subject * ceiling(weekday_n / longest_subject))
subjects = map(subjects_list, ~ rep(.x, ceiling(weekday_n / length(.x))))
df = tibble(
Days = c(weekdays, map_chr(seq_along(first_col) - weekday_n, ~paste0("Backup", .x))),
Arts = c(subjects[["Arts"]], rep(NA, length(first_col) - length(subjects[["Arts"]]))),
English = c(subjects[["English"]], rep(NA, length(first_col) - length(subjects[["English"]]))),
Maths = c(subjects[["Maths"]], rep(NA, length(first_col) - length(subjects[["Maths"]])))
)
all.equal(df, test, check.attributes = FALSE)
# TRUE