Pivot the problem table into result table. In result table, First and Last Names have been separated out from Full Name attribute.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 251
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pivot the problem table into with Power Query
Power Query solution 1 for Pivot the problem table into, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Emp ID"}, "A", "V"),
C = Table.AddIndexColumn(B, "Ix"),
D = Table.AddColumn(C, "Val", each C[V]{[Ix] + 1}),
E = Table.SelectRows(D, each Number.IsEven([Ix])),
F = Table.RemoveColumns(E, {"A", "Ix"}),
G = Table.Pivot(F, List.Distinct(F[V]), "V", "Val"),
H = Table.SplitColumn(
G,
"Full Name",
Splitter.SplitTextByDelimiter(" "),
{"First Name", "Last Name"}
),
I = Table.Sort(H, {each List.PositionOf(A[Emp ID], [Emp ID]), 0})
in
IPower Query solution 2 for Pivot the problem table into, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
{"Emp ID"},
{
{
"A",
each
let
k = List.RemoveNulls,
j = List.Skip,
a = _,
b = {"First Name", "Last Name"} & j(k([Attribute1] & [Attribute2])),
c = k([Value1] & [Value2]),
d = Text.Split(c{0}, " ") & j(c),
e = Table.SelectColumns(Table.FromRows({d}, b), f),
f = {"First Name", "Last Name"}
& j(k(List.Combine(List.Zip({[Attribute1], [Attribute2]}))))
in
e
}
}
),
Sol = Table.ExpandTableColumn(Grp, "A", Table.ColumnNames(Grp[A]{0}))
in
SolPower Query solution 3 for Pivot the problem table into, proposed by Luan Rodrigues:
let
Fonte = Table.UnpivotOtherColumns(Tabela1, {"Emp ID"}, "Atributo", "Valor"),
grp = Table.Group(
Fonte,
{"Emp ID"},
{
{
"tab",
each
let
a = Table.PromoteHeaders(Table.FromColumns(List.Split(_[Valor], 2))),
b = Table.SplitColumn(
a,
"Full Name",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"First Name", "Last Name"}
)
in
b
}
}
),
res = Table.ExpandTableColumn(grp, "tab", Table.ColumnNames(grp[tab]{0}))
in
resPower Query solution 4 for Pivot the problem table into, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"Emp ID",
{
"Data",
each [
a = List.Combine(List.Zip({[Attribute1], [Attribute2]})),
b = List.Combine(List.Zip({[Value1], [Value2]})),
c = Table.FromRows({List.RemoveNulls(b)}, List.RemoveNulls(a))
][c]
}
),
Expand = Table.ExpandTableColumn(Group, "Data", Table.ColumnNames(Group[Data]{0})),
Split = Table.SplitColumn(
Expand,
"Full Name",
each Text.Split(_, " "),
{"First Name", "Last Name"}
),
Result = Table.TransformColumnTypes(Split, {"Date of Birth", type date})
in
ResultPower Query solution 5 for Pivot the problem table into, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData251"]}[Content],
Group = Table.Group(
Source,
{"Emp ID"},
{
"G",
(t) =>
let
_L = Table.UnpivotOtherColumns(t, {"Emp ID"}, "A", "V")[V],
_Rec = Record.FromList(List.Alternate(_L, 1, 1), List.Alternate(_L, 1, 1, 1))
in
Table.FromRecords({Record.SelectFields(t{0}, {"Emp ID"}) & _Rec})
}
),
Combine = Table.Combine(Group[G]),
SplitName = Table.SplitColumn(
Combine,
"Full Name",
Splitter.SplitTextByEachDelimiter({" "}),
{"First Name", "Last Name"}
)
in
SplitNamePower Query solution 6 for Pivot the problem table into, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
F = (lst1 as list, lst2 as list) =>
[A = List.Zip({lst1, lst2}), B = List.Combine(A), C = List.Distinct(B), D = List.RemoveLastN(C)][
D
],
ColNames = F(Source[Attribute1], Source[Attribute2]),
Group = Table.Group(
Source,
"Emp ID",
{"tbl", each Table.FromRows({F([Value1], [Value2])}, ColNames)}
),
Expand = Table.ExpandTableColumn(Group, "tbl", ColNames),
Res = Table.SplitColumn(
Expand,
"Full Name",
Splitter.SplitTextByDelimiter(" "),
{"First Name", "Last Name"}
)
in
ResPower Query solution 7 for Pivot the problem table into, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.Group(
S,
{"Emp ID"},
{
{
"T",
each Table.FillDown(
Table.FromColumns(
{
{_[#"Emp ID"]{0}},
List.RemoveNulls(
List.Combine(List.Alternate(List.Skip(Table.ToColumns(_), 1), 1, 1, 1))
),
List.RemoveNulls(List.Combine(List.Alternate(List.Skip(Table.ToColumns(_), 1), 1, 1)))
},
{"Emp ID", "At", "Val"}
),
{"Emp ID"}
)
}
}
),
B = Table.Combine(A[T]),
C = Table.Pivot(B, List.Sort(List.Distinct(B[At])), "At", "Val"),
D = Table.SplitColumn(
C,
"Full Name",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"First Name", "Lat Name"}
),
E = Table.TransformColumnTypes(
D,
{{"First Name", type text}, {"Lat Name", type text}, {"Date of Birth", type date}}
)
in
EPower Query solution 8 for Pivot the problem table into, proposed by Peter Krkos:
let
Split = Table.Split(
Source,
Table.RowCount(Source)
/ List.Count(List.PositionOf(Source[Emp ID], Source{0}[Emp ID], Occurrence.All))
),
Transformed = Table.Combine(
List.Transform(
List.Zip(List.Transform(Split, Table.ToRows)),
each Table.PromoteHeaders(
Table.FromColumns(
List.Split(
{"Emp ID"} & {_{0}{0}} & List.RemoveNulls(List.Combine(List.Transform(_, List.Skip))),
2
)
)
)
)
)
in
TransformedPower Query solution 9 for Pivot the problem table into, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.RenameColumns(
Table.RemoveColumns(Source, {"Attribute1", "Value1"}),
{{"Attribute2", "Attribute"}, {"Value2", "Value"}}
),
B = Table.RenameColumns(
Table.RemoveColumns(Source, {"Attribute2", "Value2"}),
{{"Attribute1", "Attribute"}, {"Value1", "Value"}}
),
AandB = Table.Combine({B, A}),
NoNull = Table.SelectRows(AandB, each ([Attribute] <> null)),
Grp = Table.Group(
NoNull,
{"Emp ID"},
{{"Grp", each _, type table [Emp ID = number, Attribute = text, Value = any]}}
),
Index = Table.AddIndexColumn(GrpforIndex, "Index", 1, 1, Int64.Type),
WthInd = Table.ExpandTableColumn(Index, "Grp", {"Attribute", "Value"}, {"Attribute", "Value"}),
Pivot = Table.Pivot(WithIndex, List.Distinct(WithIndex[Attribute]), "Attribute", "Value"),
Sort = Table.Sort(Pivot, {{"Index", Order.Ascending}}),
Split = Table.SplitColumn(
SortIndex,
"Full Name",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"First Name", "Last Name"}
),
Result = Table.SelectColumns(
SplitName,
{
"Emp ID",
"First Name",
"Last Name",
"Gender",
"Date of Birth",
"Weight",
"Salary",
"State",
"Sales"
}
)
in
ResultPower Query solution 10 for Pivot the problem table into, proposed by Alexandre Garcia:
let
U = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
H = (x, y, z) =>
let
x = List.Alternate(x, 1, 1, y)
in
{{z} & Text.Split(x{0}, " ") & List.Skip(x)},
P = Table.ColumnNames(U){0},
L = Table.Group(
Table.UnpivotOtherColumns(U, {P}, "x", "y"),
P,
{"x", each Table.FromRows(H([y], 0, Table.FirstValue(_)), H([y], 1, P){0})}
)[x],
C = Table.RenameColumns(
Table.Combine(L),
List.Zip({List.Skip(List.FirstN(Table.ColumnNames(L{0}), 3)), {"First Name", "Last Name"}})
)
in
CPower Query solution 11 for Pivot the problem table into, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0),
tbl = [
a = Table.ColumnNames(Index),
b = List.Split(List.RemoveLastN(List.RemoveFirstN(a, 1), 1), 2),
c = List.Transform(
b,
(x) =>
Table.TransformColumnNames(
Table.SelectColumns(Index, List.FirstN(a, 1) & List.LastN(a, 1) & x),
each Text.Remove(_, {"0" .. "9"})
)
),
d = Table.Sort(Table.SelectRows(Table.Combine(c), each [Attribute] <> null), {"Index"})
][d],
grup = Table.Group(
tbl,
{"Emp ID"},
{
{
"r",
each [
a = Table.PromoteHeaders(Table.Transpose([[Attribute], [Value]])),
b = Table.SplitColumn(
a,
"Full Name",
Splitter.SplitTextByDelimiter(" "),
{"First Name", "Last Name"}
)
][b]
}
}
),
f = Table.ExpandTableColumn(
grup,
"r",
{"First Name", "Last Name"} & List.Skip(List.Distinct(tbl[Attribute]))
)
in
fPower Query solution 12 for Pivot the problem table into, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
UnpivotOthCols = Table.UnpivotOtherColumns(Source, {"Emp ID"}, "Atrr", "Value"),
GroupRows = Table.Group(
UnpivotOthCols,
{"Emp ID"},
{
{
"NT",
each [
a = _[Value],
b = List.Alternate(a, 1, 1, 1),
c = List.Alternate(a, 1, 1, 0),
d = Table.FromRows({c}, b)
][d]
}
}
),
Result = Table.ExpandTableColumn(GroupRows, "NT", Table.ColumnNames(Table.Combine(GroupRows[NT])))
in
ResultSolving the challenge of Pivot the problem table into with Excel
Excel solution 1 for Pivot the problem table into, proposed by Bo Rydobon 🇹🇭:
=LET(
i,
TOCOL(
IF(
{1,
2},
A2:A21
)
),
w,
WRAPROWS(
TOCOL(
B2:E21
),
2
),
p,
PIVOTBY(
HSTACK(
XMATCH(
i,
i
),
i
),
TAKE(
w,
,
1
),
DROP(
w,
,
1
),
SINGLE,
0,
0,
,
0
),
j,
INDEX(
p,
,
2
),
HSTACK(
IF(
j="",
A1,
j
),
VSTACK(
{"First",
"Last"}&" Name",
DROP(
TEXTSPLIT(
TEXTAFTER(
" "&INDEX(
p,
,
4
),
" ",
{1,
2}
),
" "
),
1
)
),
CHOOSECOLS(
p,
5,
3,
9,
6,
8,
7
)
)
)Excel solution 2 for Pivot the problem table into, proposed by Rick Rothstein:
=DROP(
REDUCE(
0,
UNIQUE(
A2:A21
),
LAMBDA(
a,
x,
LET(
d,
TOROW(
FILTER(
A2:E21,
A2:A21=x
)
),
n,
TAKE(
DROP(
d,
,
2
),
,
1
),
VSTACK(
a,
HSTACK(
TAKE(
d,
,
1
),
TEXTBEFORE(
n,
" "
),
TEXTAFTER(
n,
" "
),
CHOOSECOLS(
d,
5,
8,
10,
13,
15,
18
)
)
)
)
)
),
1
)
With the headers...
=REDUCE(
{"Emp ID",
"First Name",
"Last Name",
"Gender",
"Date of Birth",
"Weight",
"Salary",
"State",
"Sales"},
UNIQUE(
A2:A21
),
LAMBDA(
a,
x,
LET(
d,
TOROW(
FILTER(
A2:E21,
A2:A21=x
)
),
n,
TAKE(
DROP(
d,
,
2
),
,
1
),
VSTACK(
a,
HSTACK(
TAKE(
d,
,
1
),
TEXTBEFORE(
n,
" "
),
TEXTAFTER(
n,
" "
),
CHOOSECOLS(
d,
5,
8,
10,
13,
15,
18
)
)
)
)
)
)
With the headers (a little bit shorter,
but I am not sure,
it might be considered "cheating")...
=REDUCE(
HSTACK(
A1,
"First Name",
"Last Name",
D2,
B7,
D7,
B12,
D12,
B17
),
UNIQUE(
A2:A21
),
LAMBDA(
a,
x,
LET(
d,
TOROW(
FILTER(
A2:E21,
A2:A21=x
)
),
n,
TAKE(
DROP(
d,
,
2
),
,
1
),
VSTACK(
a,
HSTACK(
TAKE(
d,
,
1
),
TEXTBEFORE(
n,
" "
),
TEXTAFTER(
n,
" "
),
CHOOSECOLS(
d,
5,
8,
10,
& 13,
15,
18
)
)
)
)
)
)Excel solution 3 for Pivot the problem table into, proposed by 🇰🇷 Taeyong Shin:
=LET(
F,
LAMBDA(
n,
TOCOL(
CHOOSECOLS(
A2:E21,
n
),
1,
1
)
),
u,
UNIQUE(
A1:A21
),
t,
WRAPCOLS(
TOCOL(
TEXTSPLIT(
TEXTAFTER(
" "&F(
{3,
5}
),
" ",
{1,
2}
),
" "
),
2,
1
),
ROWS(
u
)-1
),
HSTACK(
u,
VSTACK(
SORTBY(
TOROW(
VSTACK(
{"First";"Last"}&"Name",
DROP(
UNIQUE(
F(
{2,
4}
)
),
1
)
)
),
{1,
2,
4,
6,
8,
3,
5,
7}
),
CHOOSECOLS(
IFERROR(
--t,
t
),
{1,
8,
5,
2,
6,
3,
7,
4}
)
)
)
)Excel solution 4 for Pivot the problem table into, proposed by Julian Poeltl:
=LET(
T,
A2:E21,
R,
ROWS(
T
)/4,
C,
TOCOL(
HSTACK(
TAKE(
T,
R
),
DROP(
CHOOSEROWS(
T,
SEQUENCE(
R,
,
R+1
)
),
,
1
),
DROP(
CHOOSEROWS(
T,
SEQUENCE(
R,
,
R*2+1
)
),
,
1
),
DROP(
T,
3*R,
1
)
),
1
),
U,
TAKE(
T,
R,
1
),
N,
INDEX(
C,
XMATCH(
U,
C
)+2
),
VSTACK(
HSTACK(
"Emp ID",
"First Name",
"Last Name",
INDEX(
C,
SEQUENCE(
,
6,
4,
2
)
)
),
HSTACK(
U,
TEXTBEFORE(
N,
" "
),
TEXTAFTER(
N,
" "
),
INDEX(
C,
XMATCH(
U,
C
)+SEQUENCE(
,
6,
4,
2
)
)
)
)
)Excel solution 5 for Pivot the problem table into, proposed by Oscar Mendez Roca Farell:
=LET(O,
DROP,
d,
A2:E21,
e,
TAKE(
d,
,
1
),
u,
UNIQUE(TOROW(IFS((CODE(
A1:E1
)=65)*(d>""),
d),
2),
1),
r,
REDUCE(
HSTACK(
A1,
{"First",
"Last"}&MID(
@u,
5,
5
),
O(
u,
,
1
)
),
UNIQUE(
e
),
LAMBDA(
i,
x,
VSTACK(
i,
HSTACK(
x,
TEXTSPLIT(
CONCAT(
O(
WRAPCOLS(
TOCOL(
IFS(
e=x,
O(
d,
,
1
)
),
3
),
2
),
1,
-1
)&" "
),
" ",
,
1
)
)
)
)
),
IFERROR(
--r,
r
))Excel solution 6 for Pivot the problem table into, proposed by Duy Tùng:
=LET(
H,
HSTACK,
I,
INDEX,
a,
H(
C2:C21,
E2:E21
),
f,
LAMBDA(
x,
TOCOL(
IFS(
a>0,
x
),
3
)
),
b,
f(
A2:A21
),
c,
f(
H(
B2:B21,
D2:D21
)
),
d,
DROP(
PIVOTBY(
H(
XMATCH(
b,
A2:A21
),
b
),
H(
XMATCH(
c,
c
),
c
),
TEXT(
f(
a
),
"0"
),
SINGLE,
,
0,
,
0
),
1,
1
),
e,
I(
d,
,
2
),
k,
DROP(
d,
1
),
u,
IFERROR(
--k,
k
),
VSTACK(
H(
A1,
{"First",
"Last"}&" Name",
DROP(
I(
d,
1
),
,
2
)
),
H(
I(
u,
,
1
),
DROP(
H(
TEXTBEFORE(
e,
" "
),
TEXTAFTER(
e,
" "
)
),
1
),
DROP(
u,
,
2
)
)
)
)Excel solution 7 for Pivot the problem table into, proposed by Sunny Baggu:
=LET(
_a,
VSTACK(
A2:A21,
A2:A21
),
_b,
VSTACK(
B2:B21,
D2:D21
),
_c,
VSTACK(
C2:C21,
E2:E21
),
_u,
UNIQUE(
_a
),
_h,
TOROW(
UNIQUE(
TOCOL(
HSTACK(
B2:B21,
D2:D21
),
3,
)
)
),
_v,
XLOOKUP(
_u & _h,
_a & _b,
_c
),
_fn1,
TEXTBEFORE(
TAKE(
_v,
,
1
),
" "
),
_fn2,
TEXTAFTER(
TAKE(
_v,
,
1
),
" "
),
VSTACK(
HSTACK(
A1,
{"First Name",
"Last Name"},
DROP(
_h,
,
1
)
),
HSTACK(
_u,
_fn1,
_fn2,
DROP(
_v,
,
1
)
)
)
)Excel solution 8 for Pivot the problem table into, proposed by Md. Zohurul Islam:
=LET(
a,
A2:A21,
b,
B2:B21,
p,
D2:D16,
u,
C2:C21,
v,
E2:E16,
id,
VSTACK(
a,
TAKE(
a,
COUNTA(
p
)
)
),
lokupArray,
VSTACK(
b,
p
),
retrnpArray,
VSTACK(
u,
v
),
d,
UNIQUE(
id
),
e,
TOROW(
UNIQUE(
lokupArray
)
),
f,
XLOOKUP(
d&e,
id&lokupArray,
retrnpArray
),
g,
TAKE(
f,
,
1
),
h,
HSTACK(
TEXTBEFORE(
g,
" "
),
TEXTAFTER(
g,
" "
)
),
hdr,
HSTACK(
"Emp id",
"First Name",
"Last Name",
DROP(
e,
,
1
)
),
j,
VSTACK(
hdr,
HSTACK(
d,
h,
DROP(
f,
,
1
)
)
),
k,
CHOOSECOLS(
j,
1,
2,
3,
7,
4,
8,
5,
9,
6
),
k
)Excel solution 9 for Pivot the problem table into, proposed by Pieter de B.:
=LET(
c,
CHOOSECOLS,
i,
TOCOL(
IFS(
ISTEXT(
c(
B2:E21,
1,
3
)
),
A2:A21
),
2
),
w,
""&WRAPROWS(
TOCOL(
B2:E21,
1
),
2
),
p,
PIVOTBY(
HSTACK(
XMATCH(
i,
A2:A21
),
i
),
c(
w,
1
),
c(
w,
2
),
SINGLE,
,
0,
,
0
),
h,
HSTACK(
c(
p,
2
),
TEXTSPLIT(
TEXTAFTER(
" "&c(
p,
4
),
" ",
{1,
2}
),
" "
),
c(
p,
5,
3,
9,
6,
8,
7
)
),
t,
TAKE(
h,
1
),
v,
VSTACK(
IFS(
t="",
"Emp ID",
t="Full",
"First Name",
t="Name",
"Last Name",
1,
t
),
DROP(
IFERROR(
--h,
h
),
1
)
),
v
)Excel solution 10 for Pivot the problem table into, proposed by Hamidi Hamid:
=LET(
rx,
XLOOKUP,
a,
A2:A21,
c,
C2:C21,
e,
E2:E21,
x,
UNIQUE(
a
),
y,
TEXTBEFORE(
rx(
x,
a,
c,
),
" ",
),
z,
TEXTAFTER(
rx(
x,
a,
c,
),
" ",
),
p,
XLOOKUP(
x,
a,
e,
),
f,
SUMIF(
a,
x,
e
),
m,
rx(
x,
IF(
D2:D21="state",
a,
0
),
E2:E21,
0
),
s,
rx(
x,
IF(
B2:B21="Salary",
a,
0
),
c,
0
),
h,
rx(
x,
IF(
B2:B21="Date of Birth",
a,
0
),
c,
0
),
n,
rx(
x,
a,
c,
,
,
-1
),
HSTACK(
x,
y,
z,
p,
h,
f,
s,
m,
n
)
)Excel solution 11 for Pivot the problem table into, proposed by Asheesh Pahwa:
=LET(
d,
DROP(
REDUCE(
"",
H10#,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B2:E21,
A2:A21=y
),
t,
TOROW(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
f
)
),
LAMBDA(
a,
v,
VSTACK(
a,
TAKE(
WRAPROWS(
INDEX(
f,
v,
),
2
),
,
-1
)
)
)
),
1
),
1
),
_t,
TAKE(
t,
,
1
),
HSTACK(
TEXTSPLIT(
_t,
" "
),
TEXTAFTER(
_t,
" "
),
DROP(
t,
,
1
)
)
)
)
)
),
1
),
VSTACK(
HSTACK(
{"First",
"Last"}&"Name",
DROP(
TOROW(
UNIQUE(
VSTACK(
B2:B21,
D2:D16
)
)
),
,
1
)
),
d
)
)Excel solution 12 for Pivot the problem table into, proposed by ferhat CK:
=LET(
a,
DROP(
PIVOTBY(
A2:A21,
B2:B21,
TEXT(
C2:E21,
0
),
SINGLE,
,
0,
,
0
),
1,
-2
),
c,
CHOOSECOLS,
VSTACK(
{"Emp ID",
"First Name",
" Last Name",
"Gender",
"Date of Birth",
"Weight",
"Salary",
"State",
"Sales"},
HSTACK(
c(
a,
1
),
TEXTBEFORE(
c(
a,
5
),
" "
),
TEXTAFTER(
c(
a,
5
),
" "
),
c(
a,
7
),
TEXT(
c(
a,
2
),
"gg.aa.yyyy"
),
c(
a,
4
),
c(
a,
8
),
c(
a,
10
),
c(
a,
11
)
)
)
)Solving the challenge of Pivot the problem table into with Python
Python solution 1 for Pivot the problem table into, proposed by Konrad Gryczan, PhD<&/strong>:
import pandas as pd
path = "PQ_Challenge_251.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:E", nrows=21)
test = pd.read_excel(path, sheet_name=0, usecols="G:O", nrows=5).rename(columns=lambda x: x.split('.')[0])
.sort_values("Emp ID").reset_index(drop=True)
input_long = pd.concat([
input[['Emp ID', 'Attribute1', 'Value1']].rename(columns={'Attribute1': 'Attr', 'Value1': 'Val'}),
input[['Emp ID', 'Attribute2', 'Value2']].rename(columns={'Attribute2': 'Attr', 'Value2': 'Val'})
])
input_long = input_long.dropna()
result = input_long.pivot(index='Emp ID', columns='Attr', values='Val').reset_index()
result[['Weight', 'Salary', 'Sales']] = result[['Weight', 'Salary', 'Sales']].apply(pd.to_numeric)
result['Date of Birth'] = pd.to_datetime(result['Date of Birth'], errors='coerce')
result = result.sort_values(by = 'Emp ID').reset_index(drop=True)
result.index.name = None
print(result.equals(test)) # True
Python solution 2 for Pivot the problem table into, proposed by Luan Rodrigues:
PY Solution!
import pandas as pd
file = "PQ_Challenge_251.xlsx"
df = pd.read_excel(file,usecols="A:E")
unpv = df.melt(id_vars=['Emp ID'],var_name='Atributo',value_name='Valor')
atr = unpv[unpv['Atributo'].str.startswith('Attribute')].reset_index(drop=True)
vlr = unpv[unpv['Atributo'].str.startswith('Value')][['Valor']].rename(columns={'Valor': 'Result'}).reset_index(drop=True)
conc = pd.concat([atr,vlr],axis=1)
conc = conc.pivot_table(values='Result',index='Emp ID',columns='Valor', aggfunc='sum',sort=False).reset_index()
Python solution 3 for Pivot the problem table into, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_251.xlsx'
df = pd.read_excel(file_path, usecols='A:E')
# Perform data manipulation
dfs = []
for emp_id in df['Emp ID'].unique():
a = df[df['Emp ID'] == emp_id]
b = [x for y in zip(a.Attribute1, a.Attribute2) for x in y if pd.notna(x)]
c = [x for y in zip(a.Value1, a.Value2) for x in y if pd.notna(x)]
d = pd.DataFrame(data=[c], columns=b)
d.insert(0, 'Emp ID', emp_id)
dfs.append(d)
df = pd.concat(dfs, ignore_index=True)
cols = df.columns.to_list()
col_names = [cols[0]] + cols[-2:] + cols[2: -2]
df = df[col_names]
df
Solving the challenge of Pivot the problem table into with Python in Excel
Python in Excel solution 1 for Pivot the problem table into, proposed by Alejandro Campos:
df = xl("A1:E21", headers=True)
pivot_df = df.pivot(index='Emp ID', columns='Attribute1', values='Value1').reset_index()
pivot_df.drop(columns=['Full Name'], inplace=True)
for attr, col in zip(['Gender', 'Weight', 'State', 'Sales'], ['Attribute2', 'Attribute2', 'Attribute2', 'Attribute1']):
temp_df = df[df[col] == attr].set_index('Emp ID')['Value2' if attr != 'Sales' else 'Value1']
pivot_df[attr] = pivot_df['Emp ID'].map(temp_df)
pivot_df['Emp ID'] = pd.Categorical(pivot_df['Emp ID'], categories=df['Emp ID'].unique(), ordered=True)
pivot_df.sort_values('Emp ID', inplace=True)
pivot_df.reset_index(drop=True, inplace=True)
pivot_df.columns.name = None
pivot_df
Python in Excel solution 2 for Pivot the problem table into, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:E21", True)
df = df.set_index("Emp ID")
cols = df.columns
splt = [df[cols[i : i + 2]] for i in range(0, len(cols), 2)]
for i in splt:
i.columns = ["A", "V"]
df = pd.concat(splt, names=["A", "V"]).reset_index()
pivot = (
df.pivot_table("V", "Emp ID", "A", "first").reset_index().rename_axis("", axis=1)
)
result = pivot[
[
"Emp ID",
"Last Name",
"Gender",
"Date of Birth",
"Weight",
"Salary",
"State",
"Sales",
]
]
result
Python in Excel solution 3 for Pivot the problem table into, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:E21", True)
df = df.set_index("Emp ID")
cols = df.columns
splt = [df[cols[i : i + 2]] for i in range(0, len(cols), 2)]
for i in splt:
i.columns = ["A", "V"]
df = pd.concat(splt, names=["A", "V"]).reset_index()
pivot = df.pivot_table("V", "Emp ID", "A", "first").reset_index()
result = pivot[
[
"Emp ID",
"Last Name",
"Gender",
"Date of Birth",
"Weight",
"Salary",
"State",
"Sales",
]
]
result
Solving the challenge of Pivot the problem table into with R
R solution 1 for Pivot the problem table into, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_251.xlsx"
input = read_excel(path, range = "A1:E21")
test = read_excel(path, range = "G1:O6")
result = bind_rows(
input %>% select(`Emp ID` = 1, Attr = 2, Val = 3),
input %>% select(`Emp ID` = 1, Attr = 4, Val = 5)
) %>%
na.omit() %>%
pivot_wider(names_from = Attr, values_from = Val) %>%
mutate(across(c(Weight, Salary, Sales), as.numeric),
`Date of Birth` = as.POSIXct(janitor::excel_numeric_to_date(as.numeric(`Date of Birth`))))
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&