Transform the problem table into result table as shown.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 243
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Unstack Columns to Rows with Power Query
Power Query solution 1 for Unstack Columns to Rows, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
H = Table.ColumnNames(Source),
S = Table.Combine(
List.Combine(
Table.Group(
Source,
"Emp ID",
{
"G",
each List.TransformMany(
List.Zip(List.Transform([Group], each Text.Split(_, ", "))),
each {_},
(i, o) =>
Table.FromRows(
{{[Emp ID]{0}} & o},
{H{0}} & List.Transform({1 .. List.Count(o)}, each H{1} & Text.From(_))
)
)
}
)[G]
)
)
in
SPower Query solution 2 for Unstack Columns to Rows, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(
A,
{"Emp ID"},
{
"All",
each
let
a = List.Transform([Group], (x) => Text.Split(x, ", "))
in
Table.TransformColumnNames(Table.FromColumns(a), each Text.Replace(_, "Column", "Group"))
}
),
C = Table.ExpandTableColumn(B, "All", Table.ColumnNames(B[All]{0}))
in
CPower Query solution 3 for Unstack Columns to Rows, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Emp ID",
{
"A",
each [S = List.Transform([Group], (f) => Text.Split(f, ", ")), R = Table.FromColumns(S)][R]
}
),
Cols = Table.ColumnNames(Table.Combine(Group[A])),
Expand = Table.ExpandTableColumn(Group, "A", Cols),
Return = Table.TransformColumnNames(Expand, each Text.Replace(_, "Column", "Group"))
in
ReturnPower Query solution 4 for Unstack Columns to Rows, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Emp ID"},
{
{
"A",
each
let
a = _,
b = a[Group],
c = List.Transform(b, each Text.Split(_, ", ")),
d = Table.FromColumns(
c,
List.Transform({1 .. List.Count(b)}, each "Group" & Text.From(_))
)
in
d
}
}
),
Sol = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0}))
in
SolPower Query solution 5 for Unstack Columns to Rows, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(
Fonte,
{"Emp ID"},
{
{
"tab",
each
let
a = [Emp ID],
b = Table.FromColumns(
List.Transform(_[Group], (x) => Text.Split(x, ", ")),
List.Transform({1 .. List.Count(a)}, (y) => "Group" & Text.From(y))
),
c = Table.AddColumn(b, "Emp ID", each a{0})
in
Table.SelectColumns(c, List.Sort(Table.ColumnNames(c)))
}
}
)[tab],
cmb = Table.Combine(grp)
in
cmbPower Query solution 6 for Unstack Columns to Rows, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"Emp ID",
{
"Data",
each [
a = List.Zip(List.Transform([Group], (x) => Text.Split(x, ", "))),
b = {"Emp ID"} & List.Transform({1 .. List.Count(a{0})}, (x) => "Group" & Text.From(x)),
c = Table.Combine(List.Transform(a, (x) => Table.FromRows({{[Emp ID]{0}} & x}, b)))
][c]
}
),
Result = Table.Combine(Group[Data])
in
ResultPower Query solution 7 for Unstack Columns to Rows, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.Group(S, {"Emp ID"}, {"G", each [Group]}),
b = Table.TransformColumns(
a,
{"G", each Table.FromRows(List.Zip(List.Transform(_, each Text.Split(_, ", "))))}
),
c = Table.ExpandTableColumn(b, "G", {"Column1", "Column2", "Column3"}),
Sol = Table.TransformColumnNames(c, each Text.Replace(_, "Column", "Group"))
in
SolPower Query solution 8 for Unstack Columns to Rows, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData243"]}[Content],
Group = Table.Group(
Source,
"Emp ID",
{
"G",
(t) =>
let
_L = List.Transform(t[Group], each Text.Split(_, ", ")),
_CN = List.Transform({1 .. Table.RowCount(t)}, each "Group" & Text.From(_)),
_T = Table.FromColumns({{t[Emp ID]{0}}} & _L, {"Emp ID"} & _CN)
in
Table.FillDown(_T, {"Emp ID"})
}
),
Result = Table.Combine(Group[G])
in
ResultPower Query solution 9 for Unstack Columns to Rows, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Emp ID"},
{
{
"tbl",
each [
T = List.Transform([Group], each Text.Split(_, ", ")),
CN = {"EMP ID"} & List.Transform({1 .. List.Count(T)}, each "Group" & Text.From(_)),
C = {List.Distinct([Emp ID])} & T,
R = Table.FromColumns(C, CN)
][R]
}
}
),
Res = Table.FillDown(Table.Combine(Grouped[tbl]), {"EMP ID"})
in
ResPower Query solution 10 for Unstack Columns to Rows, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.Group(S, {"Emp ID"}, {{"T", each _}}),
F = (x) =>
let
a = Table.AddColumn(x, "T", each Text.Split([Group], ", ")),
b = Table.FromColumns(
a[T],
List.Transform({1 .. Table.RowCount(a)}, each "Group." & Text.From(_))
),
c = Table.AddColumn(b, "Emp ID", each a[Emp ID]{0})
in
c,
B = Table.AddColumn(A, "F", each F([T])),
C = Table.Combine(B[F]),
D = Table.ReorderColumns(C, {"Emp ID", "Group.1", "Group.2", "Group.3"})
in
DPower Query solution 11 for Unstack Columns to Rows, proposed by Peter Krkos:
let
Transformed = Table.Combine(
Table.Group(
Source,
{"Emp ID"},
{
{
"T",
each Table.FillDown(
Table.FromColumns({{[Emp ID]{0}}} & List.Transform([Group], (x) => Text.Split(x, ", "))),
{"Column1"}
),
type table
}
}
)[T]
),
RenamedAndType = [
a = Table.ColumnNames(Transformed),
b = {"Emp ID"} & List.Transform({1 .. List.Count(a) - 1}, each "Group " & Text.From(_)),
c = Table.RenameColumns(Transformed, List.Zip({a, b})),
d = Table.TransformColumnTypes(
c,
{{"Emp ID", Int64.Type}} & List.Transform(List.Skip(b), (x) => {x, type text})
)
][d]
in
RenamedAndTypePower Query solution 12 for Unstack Columns to Rows, proposed by Khanh Lam chi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transcol1 = Table.TransformColumns(Source, {"Group", each Text.Split(_, ", ")}),
Sort = Table.Sort(Transcol1, {{"Emp ID", Order.Ascending}}),
Group = Table.Group(Sort, {"Emp ID"}, {"Count", each [Group]}),
Transcol2 = Table.TransformColumns(
Group,
{
"Count",
(t) =>
Table.FromColumns(t, List.Transform({1 .. List.Count(t)}, each "Group " & Text.From(_)))
}
),
Exp = Table.ExpandTableColumn(
Transcol2,
"Count",
Table.ColumnNames(Table.Combine(Transcol2[Count]))
)
in
ExpSolving the challenge of Unstack Columns to Rows with Excel
Excel solution 1 for Unstack Columns to Rows, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:A10,
REDUCE(
HSTACK(
A1,
B1&SEQUENCE(
,
MAX(
COUNTIF(
z,
z
)
)
)
),
SORT(
UNIQUE(
z
)
),
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
IFNA(
HSTACK(
v,
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
0,
,
FILTER(
B2:B10,
z=v
)
),
", ",
0,
,
,
""
)
)
),
v
)
),
""
)
)
)
)Excel solution 2 for Unstack Columns to Rows, proposed by Rick Rothstein:
=DROP(
IFERROR(
REDUCE(
"",
UNIQUE(
A2:A10
),
LAMBDA(
a,
n,
VSTACK(
a,
LET(
b,
B2:B10,
f,
FILTER(
b,
OFFSET(
b,
,
-1
)=n
),
t,
TRANSPOSE(
TEXTSPLIT(
TEXTAFTER(
", "&f,
", ",
SEQUENCE(
,
1+MAX(
LEN(
f
)-LEN(
SUBSTITUTE(
f,
",",
""
)
)
)
)
),
", "
)
),
HSTACK(
n*SEQUENCE(
ROWS(
t
),
,
,
0
),
t
)
)
)
)
),
""
),
1
)Excel solution 3 for Unstack Columns to Rows, proposed by 🇰🇷 Taeyong Shin:
=LET(r,
REDUCE(
A1,
UNIQUE(
A2:A10
),
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
IFNA(
HSTACK(
v,
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
"|",
,
REPT(
B2:B10,
A2:A10=v
)
),
", ",
"|",
,
,
""
)
)
),
v
)
),
""
)
)
),
IF((r="")*(SEQUENCE(
ROWS(
r
)
)=1),
B1&SEQUENCE(
,
COLUMNS(
r
),
0
),
r))Excel solution 4 for Unstack Columns to Rows, proposed by Oscar Mendez Roca Farell:
=LET(
a,
A2:A11,
REDUCE(
HSTACK(
A1,
B1&SEQUENCE(
,
MAX(
COUNTIF(
a,
a
)
)
)
),
UNIQUE(
a
),
LAMBDA(
i,
x,
IFNA(
VSTACK(
i,
IFNA(
HSTACK(
x,
TRANSPOSE(
TEXTSPLIT(
CONCAT(
FILTER(
B2:B11,
a=x
)&"|"
),
", ",
"|",
1,
,
""
)
)
),
x
)
),
""
)
)
)
)Excel solution 5 for Unstack Columns to Rows, proposed by Duy Tùng:
=LET(
a,
DROP(
REDUCE(
0,
SORT(
UNIQUE(
A2:A10
)
),
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
IFNA(
HSTACK(
y,
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
"/",
,
FILTER(
B2:B10,
A2:A10=y
)
),
", ",
& "/",
,
,
""
)
)
),
y
)
),
""
)
)
),
1
),
VSTACK(
HSTACK(
A1,
B1&SEQUENCE(
,
COLUMNS(
a
)-1
)
),
a
)
)Excel solution 6 for Unstack Columns to Rows, proposed by Sunny Baggu:
=LET(
_u, UNIQUE(A2:A10),
_r, IFNA(
DROP(
REDUCE(
"💐",
_u,
LAMBDA(x, y,
VSTACK(
x,
IFNA(
HSTACK(
y,
IFNA(
DROP(
REDUCE(
"🌼",
FILTER(B2:B10, A2:A10 = y),
LAMBDA(a, v, HSTACK(a, TEXTSPLIT(v, , ", ")))
),
,
1
),
""
)
),
y
)
)
)
),
1
),
""
),
VSTACK(HSTACK(A1, B1 & SEQUENCE(, COLUMNS(_r) - 1)), _r)
)Excel solution 7 for Unstack Columns to Rows, proposed by LEONARD OCHEA 🇷🇴:
=LET(
i,
A2:A10,
m,
SUBSTITUTE(
B2:B10,
", ",
""
),
n,
MAX(
LEN(
m
)
),
s,
SEQUENCE(
,
n
),
d,
MID(
m,
s,
1
),
F,
LAMBDA(
x,
TOCOL(
IFS(
d>"",
x
),
3
)
),
g,
B1&MAP(
i,
LAMBDA(
x,
COUNTIF(
A2:x,
x
)
)
),
p,
PIVOTBY(
HSTACK(
F(
i
),
F(
s
)
),
F(
g
),
F(
d
),
SINGLE,
,
0,
,
0
),
HSTACK(
TAKE(
p,
,
1
),
DROP(
p,
,
2
)
)
)Excel solution 8 for Unstack Columns to Rows, proposed by Md. Zohurul Islam:
=LET(
id,
A2:A10,
grp,
B2:B10,
u,
UNIQUE(
id
),
v,
MAX(
MAP(
grp,
LAMBDA(
z,
COUNTA(
TEXTSPLIT(
z,
", "
)
)
)
)
),
hdr,
HSTACK(
A1,
B1 & SEQUENCE(
,
v
)
),
w,
IFNA(
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
LET(
a,
IFNA(
DROP(
REDUCE(
"",
FILTER(
grp,
id = y
),
LAMBDA(
p,
q,
HSTACK(
p,
TEXTSPLIT(
q,
,
", "
)
)
)
),
,
1
),
""
),
b,
IFNA(
HSTACK(
y,
a
),
y
),
d,
VSTACK(
x,
b
),
d
)
)
),
1
),
""
),
result,
VSTACK(
hdr,
w
),
result
)Excel solution 9 for Unstack Columns to Rows, proposed by Asheesh Pahwa:
=LET(
e,
A2:A10,
g,
B2:B10,
u,
UNIQUE(
e
),
I,
IFNA(
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
g,
e=y
),
HSTACK(
y,
DROP(
REDUCE(
"",
f,
LAMBDA(
a,
v,
HSTACK(
a,
TEXTSPLIT(
v,
,
", "
)
)
)
),
,
1
)
)
)
)
)
),
1
),
""
),
HSTACK(
SCAN(
"",
TAKE(
I,
,
1
),
LAMBDA(
x,
y,
IF(
ISNUMBER(
y
),
y,
x
)
)
),
DROP(
I,
,
1
)
)
)Excel solution 10 for Unstack Columns to Rows, proposed by Jaroslaw Kujawa:
=DROP(IFNA(REDUCE("";
UNIQUE(
A2:A10
);
LAMBDA(a;
x;
LET(xx;
A2:B10;
y;
FILTER(
xx;
TAKE(
xx;
;
1
)=x
);
nc;
(LEN(
TAKE(
y;
;
-1
)
)-LEN(
SUBSTITUTE(
TAKE(
y;
;
-1
);
", ";
)
))/LEN(
", "
);
max;
MAX((LEN(
TAKE(
y;
;
-1
)
)-LEN(
SUBSTITUTE(
TAKE(
y;
;
-1
);
", ";
)
))/LEN(
", "
));
bt;
TEXTSPLIT(
CONCAT(
TAKE(
y;
;
-1
)&REPT(
", ";
max-nc
)&";"
);
", ";
";"
);
VSTACK(
a;
DROP(
TRANSPOSE(
VSTACK(
1*TEXTSPLIT(
REPT(
x&", ";
COLUMNS(
bt
)
);
", "
);
bt
)
);
-1;
-1
)
))));
"");
1)Excel solution 11 for Unstack Columns to Rows, proposed by Songglod P.:
=REDUCE(
{"Emp ID",
"Group 1",
"Group 2",
"Group 3"},
UNIQUE(
A2:A10
),
LAMBDA(
a,
v,
LET(
g,
FILTER(
B2:B10,
A2:A10=v
),
t,
DROP(
REDUCE(
0,
g,
LAMBDA(
a,
v,
HSTACK(
a,
TOCOL(
TEXTSPLIT(
v,
", "
)
)
)
)
),
,
1
),
IFNA(
VSTACK(
a,
HSTACK(
SEQUENCE(
ROWS(
t
),
,
v,
0
),
t
)
),
""
)
)
)
)Solving the challenge of Unstack Columns to Rows with Python
Python solution 1 for Unstack Columns to Rows, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_243.xlsx"
input = pd.read_excel(path,usecols="A:B", nrows=9)
test = pd.read_excel(path, usecols="D:G", nrows=12).rename(columns=lambda x: x.split('.')[0])
input['Group_2'] = input.groupby('Emp ID').cumcount().add(1).astype(str).radd('Group')
input = input.sort_values('Emp ID').assign(Group=input['Group'].str.split(', ')).explode('Group')
input['x'] = input.groupby(['Emp ID', 'Group_2']).cumcount().add(1)
pivot_table = input.pivot_table(index=['Emp ID', 'x'], columns='Group_2', values='Group', aggfunc='first')
pivot_table = pivot_table.reset_index().drop(columns='x').rename_axis(None, axis=1)
print(pivot_table.equals(test)) # True
Python solution 2 for Unstack Columns to Rows, proposed by Luan Rodrigues:
import pandas as pd
file = "PQ_Challenge_243.xlsx"
df = pd.read_excel(file,usecols="A:B")
def tab(x):
a = pd.DataFrame([i.split(', ') for i in x['Group'] ]).T
b = ["Group"+str(i) for i in range(1,len(a.columns)+1)]
c = a.rename(columns=dict(zip(a.columns,b)))
return c
grp = df.groupby('Emp ID').apply(tab).reset_index()
del grp['level_1']
print(grp)
Solving the challenge of Unstack Columns to Rows with Python in Excel
Python in Excel solution 1 for Unstack Columns to Rows, proposed by Alejandro Campos:
data = xl("A1:B10", headers=True)
final_result = (
data.groupby("Emp ID")["Group"]
.apply(lambda x: pd.DataFrame([row.split(", ") for row in x]).T)
.fillna("")
.rename(columns=lambda i: f"Group{i+1}")
.reset_index(level=1, drop=True)
.reset_index()
)
final_result
Python in Excel solution 2 for Unstack Columns to Rows, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:B10", headers=True)
def MyFun(d):
split = [i.split(", ") for i in d["Group"]]
return pd.DataFrame(split).transpose()
group = data.groupby("Emp ID").apply(MyFun).fillna("")
group.columns = [f"Group{i+1}" for i in group.columns]
result = group.reset_index().drop(columns="level_1")
result
Solving the challenge of Unstack Columns to Rows with R
R solution 1 for Unstack Columns to Rows, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_243.xlsx"
input = read_excel(path, range = "A1:B10")
test = read_excel(path, range = "D1:G12")
result = input %>%
mutate(Group_2 = paste0("Group", row_number()), .by = `Emp ID`) %>%
arrange(`Emp ID`) %>%
separate_rows(Group, sep = ", ") %>%
mutate(x = row_number(), .by = c(`Emp ID`, Group_2)) %>%
pivot_wider(names_from = Group_2, values_from = Group) %>%
select(-x)
all.equal(result, test)
#> [1] TRUE
&&