Transpose the data as shown.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 277
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Transpose the data as shown with Power Query
Power Query solution 1 for Transpose the data as shown, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform(
Table.ToColumns(A),
(v) =>
Text.Split(
Text.Combine(List.Transform(v, each if _ = null then "" else Text.From(_)), ", "),
", "
)
),
C = List.Accumulate(B{0}, {}, (x, y) => x & {if Text.Length(y) > 1 then y else List.Last(x)}),
D = Table.FromRows(
List.Select(List.Zip({C} & B), (w) => w{2} <> ""),
{"Country", "Data1", "Data2"}
),
E = Table.Pivot(D, List.Distinct(D[Data1]), "Data1", "Data2", each Number.From(_{0}?)),
F = Table.Sort(E, each List.PositionOf(D[Country], [Country]))
in
FPower Query solution 2 for Transpose the data as shown, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Pros = Table.Group(
Source,
"Data2",
{
"A",
each
let
a = _,
b = Table.TransformColumns(a, {}, each try Text.Split(_, ", ") otherwise {_}),
c = Table.AddColumn(b, "B", each Table.FromColumns(Record.ToList(_)))[B]
in
c
},
0,
(x, y) => Number.From(y = null)
)[A],
Sol = Table.Combine(
List.Transform(
Pros,
each
let
a = Table.Transpose(Table.Combine(_)),
b = Table.FillDown(a, {"Column1"}),
c = Table.PromoteHeaders(b),
d = Table.RenameColumns(c, {Table.ColumnNames(c){0}, "Country"})
in
d
)
)
in
SolPower Query solution 3 for Transpose the data as shown, proposed by Luan Rodrigues:
let
Fonte = Table.FromRecords(
Table.TransformRows(
Tabela1,
each _
& [
Country = if [Data2] = null then "Country" else [Data1],
Data2 = if [Data2] = null then [Data1] else [Data2]
]
)
)[[Country], [Data2]],
fx = (x) =>
let
add = Table.AddColumn(
x,
"tab",
each List.Zip(List.Transform(Record.FieldValues(_), (y) => Text.Split(Text.From(y), ", ")))
)[tab],
cmb = Table.FromColumns(List.Combine(add)),
pmv = Table.PromoteHeaders(cmb)
in
pmv,
grp = Table.Group(
Fonte,
"Country",
{{"tabela", each fx(_)}},
0,
(a, b) => Number.From(b = "Country")
)[tabela],
cmb = Table.Combine(grp)
in
cmbPower Query solution 4 for Transpose the data as shown, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangeType = Table.TransformColumnTypes(Source, {{"Data2", type text}, {"Data1", type text}}),
AddeCountry = Table.SelectRows(
Table.FillDown(
Table.AddColumn(ChangeType, "Cou", each if [Data2] is null then [Data1] else null),
{"Cou"}
),
each ([Data2] <> null)
),
AddData = Table.AddColumn(
AddeCountry,
"Custom",
each List.Zip({Text.Split([Data1], ", "), Text.Split([Data2], ", ")})
),
SplitData = Table.SplitColumn(
Table.TransformColumns(
Table.ExpandListColumn(AddData, "Custom"),
{"Custom", each Text.Combine(_, ",")}
),
"Custom",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"1", "2"}
),
PivotCols = Table.Pivot(
Table.RemoveColumns(SplitData, {"Data1", "Data2"}),
List.Distinct(SplitData[#"1"]),
"1",
"2"
)
in
PivotColsPower Query solution 5 for Transpose the data as shown, proposed by An Nguyen:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = List.Combine(List.Transform(S[Data1], each try Text.Split(_, ", ") otherwise _)),
C = List.Accumulate(
T,
{},
(s, c) => s & {if Text.Start(c, 1) = Text.Lower(Text.Start(c, 1)) then List.Last(s) else c}
),
N = List.Combine(
List.Transform(
S[Data2],
each try
List.Transform(Text.Split(_, ", "), (x) => Number.FromText(x))
otherwise
{Replacer.ReplaceValue(_, null, "")}
)
),
F = List.Select(List.Zip({C, T, N}), each _{2} <> ""),
R = Table.FromRows(F, {"Country", "C2", "C3"})
in
Table.Sort(
Table.Pivot(R, List.Distinct(R[C2]), "C2", "C3"),
each List.PositionOf(R[Country], [Country])
)Power Query solution 6 for Transpose the data as shown, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumnTypes(S, {{"Data1", type text}, {"Data2", type text}}),
b = Table.AddColumn(a, "Country", each if [Data2] = null then [Data1] else null),
c = Table.SelectRows(Table.FillDown(b, {"Country"}), each [Data2] <> null),
d = Table.TransformColumns(
c,
{{"Data1", each Text.Split(_, ", ")}, {"Data2", each Text.Split(_, ", ")}}
),
e = Table.AddColumn(d, "Z", each List.Zip({[Data1], [Data2]}))[[Country], [Z]],
f = Table.TransformColumns(Table.ExpandListColumn(e, "Z"), {"Z", each Table.FromRows({_})}),
g = Table.ExpandTableColumn(f, "Z", {"Column1", "Column2"}),
h = Table.Pivot(g, List.Distinct(g[Column1]), "Column1", "Column2"),
Sol = Table.Sort(h, {each List.PositionOf(List.Distinct(g[Country]), [Country])})
in
SolPower Query solution 7 for Transpose the data as shown, proposed by Eric Laforce:
let
fxGroup = (t) =>
let
_T = Table.TransformColumns(Table.Skip(t), {}, each Text.Split(Text.From(_), ", "))
in
Table.FromRows(
{{t[Data1]{0}} & List.Combine(_T[Data2])},
{"Country"} & List.Combine(_T[Data1])
),
Source = Excel.CurrentWorkbook(){[Name = "tData277"]}[Content],
Group = Table.Group(
Source,
{"Data2"},
{"G", each fxGroup(_)},
GroupKind.Local,
(x, y) => Byte.From(y[Data2] = null)
),
Combine = Table.Combine(Group[G])
in
CombinePower Query solution 8 for Transpose the data as shown, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Recs = List.Accumulate(Table.ToRows(Source), {}, Fun),
Fun = (a, v) =>
[
A = if v{1} = null then a & {[Country = v{0}]} else List.RemoveLastN(a) & {B},
B = List.Last(a)
& Record.FromList(try Text.Split(v{1}, ", ") otherwise {v{1}}, Text.Split(v{0}, ", "))
][A],
Res = Table.Combine(List.Transform(Recs, each Table.FromRecords({_})))
in
ResPower Query solution 9 for Transpose the data as shown, proposed by Antriksh Sharma:
let
Source = Table,
A = Table.TransformColumns(Source, {}, each Text.Split(_, ", ")),
B = Table.TransformColumnTypes(
Table.Combine(
Table.CombineColumns(
A,
Table.ColumnNames(A),
each Table.FromColumns(_, Table.ColumnNames(Source)),
"x"
)[x]
),
{"Data2", Int64.Type}
),
C = Table.Group(
B,
{"Data1", "Data2"},
{
"T",
each
let
a = _,
b = Table.Skip(_),
c = Table.PromoteHeaders(
Table.Transpose(Table.FromRows({{"Country", [Data1]{0}}} & Table.ToRows(b)))
)
in
c
},
GroupKind.Local,
(x, y) => Byte.From(y[Data2] is null)
),
T = Table.Combine(C[T])
in
TPower Query solution 10 for Transpose the data as shown, proposed by Rafael González B.:
let
Source = Question_Table,
Fx_Tab = (tbl as table) =>
let
N = tbl{0}[Data1],
V =
let
T = Table.Skip(tbl),
AC = Table.AddColumn(T, "Union",
each List.Combine({Text.Split([Data1] & ", " & Text.From([Data2]), ", ")}))
in
AC[Union],
U = List.Transform(List.Combine(V), each try Number.From(_) otherwise _),
C1 = List.Select(U, each _ is text ),
C2 = List.Select(U, each _ is number ),
C0 = List.Repeat({N}, List.Count(C1)),
TFC = Table.FromColumns({C0, C1, C2}, {"Country", "C1", "C2"}),
Pivoted = Table.Pivot(TFC, List.Distinct(TFC[C1]), "C1", "C2", List.Sum)
in
Pivoted,
Group = Table.Group(Source, "Data2", {{"All", each Fx_Tab(_)}},0, (x,y) => Number.From(y is null)),
Result = Table.Combine(Group[All])
in
Result
🧙♂️ 🧙♂️
Power Query solution 11 for Transpose the data as shown, proposed by Peter Krkos:
let
Transformed = Table.Combine(
Table.AddColumn(
Table.AddIndexColumn(
Table.Group(
Source,
"Data2",
{
"T",
(r) =>
Table.FromRows(
List.TransformMany(
Table.ToRows(Table.Skip(r)),
each List.Zip(
List.Transform(
_,
(x) => List.Transform(try Text.Split(x, ", ") otherwise {x}, Value.FromText)
)
),
(x, y) => {r{0}[Data1]} & y
),
type table [Country = text, A = text, B = Int64.Type]
)
},
0,
(x, y) => Byte.From(y = null)
),
"i"
),
"T2",
(w) => Table.AddColumn(w[T], "i", (z) => w[i])
)[T2]
),
Pivoted = Table.RemoveColumns(
Table.Sort(Table.Pivot(Transformed, List.Distinct(Transformed[A]), "A", "B"), "i"),
"i"
)
in
PivotedPower Query solution 12 for Transpose the data as shown, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Country = Table.FillDown(
Table.AddColumn(Source, "Country", each if [Data2] = null then [Data1] else null),
{"Country"}
),
NoNull = Table.SelectRows(Country, each ([Data2] <> null)),
TextSplit = Table.TransformColumns(
NoNull,
{{"Data1", each Text.Split(_, ", ")}, {"Data2", each Text.Split(Text.From(_), ", ")}}
),
Table = Table.AddColumn(TextSplit, "Custom", each Table.FromColumns({[Data1], [Data2]}))[
[Country],
[Custom]
],
Expand = Table.ExpandTableColumn(Table, "Custom", {"Column1", "Column2"}, {"Label", "Value"}),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[Label]), "Label", "Value"),
Custom1 = Table.Sort(Pivot, each List.PositionOf(Country[Country], [Country]))
in
Custom1Power Query solution 13 for Transpose the data as shown, proposed by Alexandre Garcia:
[
H = List.Zip,
P = Table.TransformColumns(Table.Skip(x), {}, each Text.Split(Text.From(_),", ")),
L = {{"Country", Table.FirstValue(x)}} & List.Combine(Table.ToList(P, each H(_))),
C = ((x)=> Table.FromRows({x{1}},x{0})) (H(L))
]
[C]}, 0, (x,y)=> Byte.From(y = null)) [x])
Power Query solution 14 for Transpose the data as shown, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
A = Table.AddColumn(source, "Country", each if [Data2] = null then [Data1] else null),
B = Table.FillDown(A, {"Country"}),
C = Table.SelectRows(B, each [Data2] <> null),
D = Table.AddColumn(
C,
"Helper",
each Table.FromRecords(
List.Transform(
List.Zip({Text.Split([Data1], ", "), Text.Split(Text.From([Data2]), ", ")}),
each Record.FromList(_, {"Letter", "Value"})
)
)
),
E = Table.ExpandTableColumn(D, "Helper", {"Letter", "Value"})[[Country], [Letter], [Value]],
tblSort = Table.AddIndexColumn(
Table.FromList(List.Distinct(E[Country]), Splitter.SplitByNothing(), {"Country"}),
"SortOrder",
1,
1
),
F = Table.Pivot(E, List.Distinct(E[Letter]), "Letter", "Value"),
G = Table.Join(F, "Country", tblSort, "Country"),
H = Table.Sort(G, {{"SortOrder", Order.Ascending}}),
I = Table.RemoveColumns(H, {"SortOrder"})
in
IPower Query solution 15 for Transpose the data as shown, proposed by Fredson Alves Pinho:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
zip = Table.AddColumn(
Fonte,
"zip",
each
if [Data2] = null then
Table.FromRecords({[Country = [Data1]]})
else
Table.Transpose(
Table.FromList({[Data1], [Data2]}, each Text.Split(Text.From(_), ", ")),
{"A", "B"}
)
)[[zip]],
tbl = Table.FillDown(Table.ExpandTableColumn(zip, "zip", {"Country", "A", "B"}), {"Country"}),
pvt = Table.Pivot(tbl, List.Distinct(Table.SelectRows(tbl, each ([A] <> null))[A]), "A", "B")
in
pvtPower Query solution 16 for Transpose the data as shown, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
"Data1",
{
"B",
(x) =>
Table.FromColumns(
{List.Union(List.Transform(List.Skip(x[Data1]), each Text.Split(_, ", ")))}
& {List.Union(List.Transform(List.Skip(x[Data2]), each Text.Split(Text.From(_), ", ")))},
{"P", "V"}
)
},
0,
(x, y) => Byte.From(List.Contains({"A" .. "Z"}, Text.Start(y, 1)))
),
Exp = Table.ExpandTableColumn(Grp, "B", {"P", "V"}),
Res = Table.RenameColumns(
Table.Sort(
Table.Pivot(Exp, List.Distinct(Exp[P]), "P", "V"),
each List.PositionOf(Grp[Data1], [Data1])
),
{"Data1", "Country"}
)
in
ResPower Query solution 17 for Transpose the data as shown, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
"Data1",
{
"A",
each [
a = _[Data1],
b = _[Data2],
c = _[Data1]{0},
d = each List.Combine(List.Transform(List.Skip(_), each Text.Split(Text.From(_), ", "))),
f = Table.FromRows({{c} & d(b)}, {"Country"} & d(a))
][f]
},
0,
(x, y) => Byte.From(List.Contains({"A" .. "Z"}, Text.Start(y, 1)))
),
Res = Table.Combine(Grp[A])
in
ResSolving the challenge of Transpose the data as shown with Excel
Excel solution 1 for Transpose the data as shown, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A10,
b,
B2:B10,
L,
LAMBDA(
x,
TEXTSPLIT(
ARRAYTOTEXT(
x
),
,
", "
)
),
d,
L(
b
),
HSTACK(
VSTACK(
"Country",
FILTER(
a,
b=0
)
),
DROP(
PIVOTBY(
SCAN(
0,
d="",
SUM
),
L(
a
),
--d,
SUM,
,
0,
,
0,
,
d>""
),
,
1
)
)
)Excel solution 2 for Transpose the data as shown, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
B2:B10,
L,
LAMBDA(
x,
HSTACK(
XMATCH(
x,
x
),
x
)
),
g,
GROUPBY(
L(
SCAN(
,
IFS(
d="",
A2:A10
),
LAMBDA(
a,
v,
IFNA(
v,
a
)
)
)
),
A2:B10,
ARRAYTOTEXT,
,
0,
,
d>0
),
F,
LAMBDA(
x,
TEXTSPLIT(
ARRAYTOTEXT(
x
),
,
", "
)
),
n,
TAKE(
g,
,
-1
),
DROP(
PIVOTBY(
L(
F(
REGEXREPLACE(
n,
"d+",
INDEX(
g,
,
2
)
)
)
),
F(
INDEX(
g,
,
3
)
),
--F(
n
),
SUM,
,
0,
,
0
),
,
1
)
)Excel solution 3 for Transpose the data as shown, proposed by Duy Tùng:
=LET(
f,
LAMBDA(
v,
TEXTSPLIT(
ARRAYTOTEXT(
v
),
,
", "
)
),
a,
f(
A2:A10
),
b,
f(
B2:B10
),
c,
SCAN(
,
a,
LAMBDA(
x,
y,
IF(
LEN(
y
)>1,
y,
x
)
)
),
d,
DROP(
PIVOTBY(
HSTACK(
XMATCH(
c,
c
),
c
),
a,
--b,
SUM,
,
0,
,
0,
,
b>""
),
,
1
),
IF(
TAKE(
d,
1
)&TAKE(
d,
,
1
)="",
"Country",
d
)
)
#2: =LET(
H,
HSTACK,
f,
LAMBDA(
v,
TEXTSPLIT(
ARRAYTOTEXT(
v
),
,
", "
)
),
a,
f(
A2:A10
),
b,
f(
B2:B10
),
c,
SCAN(
,
a,
LAMBDA(
x,
y,
IF(
LEN(
y
)>1,
y,
x
)
)
),
d,
TOROW(
UNIQUE(
FILTER(
a,
LEN(
a
)=1
)
)
),
REDUCE(
H(
"Country",
d
),
UNIQUE(
c
),
LAMBDA(
x,
y,
LET(
a,
FILTER(
H(
a,
b
),
c=y
),
IFNA(
VSTACK(
x,
H(
y,
--VLOOKUP(
d,
a,
2,
)
)
),
""
)
)
)
)
)
#3: =LET(
R,
ARRAYTOTEXT,
a,
A2:A10,
b,
B2:B10,
c,
UNIQUE(
TEXTSPLIT(
R(
FILTER(
a,
b>0
)
),
", "
),
1
),
d,
SCAN(
0,
b,
LAMBDA(
x,
y,
IF(
y=0,
@+A10:y,
x
)
)
),
REDUCE(
HSTACK(
"Country",
E1:H1
),
UNIQUE(
d
),
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
HSTACK(
y,
--HLOOKUP(
E1:H1,
TEXTSPLIT(
TEXTJOIN(
"/",
,
BYCOL(
FILTER(
a:b,
d=y
),
R
)
),
", ",
"/"
),
2,
)
)
),
""
)
)
)
)Excel solution 4 for Transpose the data as shown, proposed by Sunny Baggu:
=LET(
_c,
B2:B10 = "",
_a,
SCAN(
"",
IF(
_c,
A2:A10,
""
),
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
),
_b,
FILTER(
_a,
1 - _c
),
_d,
FILTER(
A2:A10,
1 - _c
),
_e,
FILTER(
B2:B10,
1 - _c
),
_u,
FILTER(
A2:A10,
_c
),
_h,
UNIQUE(
TEXTSPLIT(
ARRAYTOTEXT(
_d
),
", "
),
1
),
_v,
REDUCE(
HSTACK(
"Country",
_h
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_d1,
TEXTSPLIT(
ARRAYTOTEXT(
FILTER(
_d,
_b = y
)
),
,
", "
),
_e1,
1 * TEXTSPLIT(
ARRAYTOTEXT(
FILTER(
_e,
_b = y
)
),
,
", "
),
HSTACK(
y,
XLOOKUP(
_h,
_d1,
_e1,
""
)
)
)
)
)
),
_v
)Excel solution 5 for Transpose the data as shown, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A2:A10,
b,
B2:B10,
i,
INDEX,
F,
LAMBDA(
x,
y,
i(
TEXTSPLIT(
TEXTJOIN(
", ",
,
IF(
b="",
x&"|",
y
)
),
"|",
", "
),
,
1
)
),
w,
F(
b,
a
),
p,
SCAN(
,
IF(
w="",
1
),
SUM
),
g,
DROP(
PIVOTBY(
HSTACK(
p,
i(
F(
a,
""
),
p
)
),
w,
F(
b,
b
),
SINGLE,
,
0,
,
0,
,
w>""
),
,
1
),
IFERROR(
--g,
g
)
)Excel solution 6 for Transpose the data as shown, proposed by An Nguyen:
=LET(
f,
INDEX,
n,
TEXTSPLIT(
ARRAYTOTEXT(
B2:B10
),
,
", "
),
t,
REDUCE(
,
TOCOL(
A2:A10
),
LAMBDA(
a,
v,
VSTACK(
a,
TEXTSPLIT(
v,
,
", "
)
)
)
),
d,
HSTACK(
SCAN(
,
t,
LAMBDA(
a,
v,
IF(
REGEXTEST(
v,
"^[a-z]"
),
TAKE(
a,
-1
),
v
)
)
),
t,
n
),
c,
FILTER(
d,
TAKE(
d,
,
-1
)<>""
),
SORTBY(
PIVOTBY(
f(
c,
,
1
),
f(
c,
,
2
),
f(
c,
,
3
),
SINGLE,
,
0,
,
0
),
VSTACK(
"",
UNIQUE(
f(
c,
,
1
)
)
)
)
)Excel solution 7 for Transpose the data as shown, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A10,
v,
B2:B10,
w,
UNIQUE(
SCAN(
,
IF(
v="",
u,
""
),
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
)
),
z,
BYROW(
FILTER(
HSTACK(
u,
v
),
v<>""
),
ARRAYTOTEXT
),
g,
IFNA(
DROP(
REDUCE(
"",
z,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
y,
", "
),
b,
WRAPCOLS(
a,
COUNTA(
a
)/2
),
d,
VSTACK(
x,
b
),
d
)
)
),
1
),
""
),
h,
IFERROR(
--g,
g
),
i,
TOCOL(
IFNA(
w,
SEQUENCE(
,
ROWS(
h
)/ROWS(
w
)
)
)
),
j,
PIVOTBY(
i,
TAKE(
h,
,
1
),
DROP(
h,
,
1
),
SUM,
0,
0,
,
0
),
j
)Excel solution 8 for Transpose the data as shown, proposed by Pieter de B.:
=LET(
a,
A2:A10,
b,
B2:B10,
L,
LAMBDA(
x,
TEXTSPLIT(
TEXTAFTER(
", "&x,
", ",
SEQUENCE(
,
84
)
),
", "
)
),
y,
--L(
b
),
Z,
LAMBDA(
x,
TOCOL(
IFS(
y,
x
),
2
)
),
p,
PIVOTBY(
Z(
SCAN(
"",
a,
LAMBDA(
x,
y,
IF(
CODE(
y
)<97,
y,
x
)
)
)
),
Z(
L(
a
)
),
Z(
y
),
SUM,
,
0,
,
0
),
q,
IF(
SCAN(
,
p,
CONCAT
)="",
"Country",
p
),
SORTBY(
q,
XMATCH(
TAKE(
p,
,
1
),
VSTACK(
,
a
)
)
)
)Excel solution 9 for Transpose the data as shown, proposed by Asheesh Pahwa:
=LET(
s,
SCAN(
"",
B2:B10,
LAMBDA(
x,
y,
IF(
y="",
OFFSET(
y,
0,
-1
),
x
)
)
),
u,
UNIQUE(
s
),
d,
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
A2:B10,
s=y
),
d,
DROP(
f,
1
),
t,
TEXTSPLIT(
ARRAYTOTEXT(
d
),
,
", "
),
txt,
FILTER(
t,
ISERROR(
--t
)
),
IFNA(
HSTACK(
y&txt,
TOCOL(
--t,
3
)
),
y
)
)
)
)
),
1
),
i,
INDEX(
d,
,
1
),
e,
TOROW(
UNIQUE(
RIGHT(
i
)
)
),
c,
u&e,
h,
HSTACK(
D1,
e
),
VSTACK(
h,
HSTACK(
u,
XLOOKUP(
c,
i,
TAKE(
d,
,
-1
),
""
)
)
)
)Excel solution 10 for Transpose the data as shown, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
f,
LAMBDA(
d,
DROP(
REDUCE(
"",
d,
LAMBDA(
a,
x,
VSTACK(
a,
IFERROR(
TEXTSPLIT(
x,
,
", "
),
""
)
)
)
),
1
)
),
r,
f(
A2:A10
),
d,
f(
B2:B10
),
c,
LET(
l,
IF(
d="",
r
),
d,
SCAN(
0,
l,
LAMBDA(
a,
x,
IF(
x<>FALSE,
x,
a
& )
)
),
d
),
t,
VSTACK(
HSTACK(
"country",
TOROW(
UNIQUE(
FILTER(
r,
LEN(
r
)=1
)
)
)
),
SORTBY(
DROP(
PIVOTBY(
c,
r,
d,
CONCAT,
0,
0,
,
0,
,
d<>""
),
1
),
UNIQUE(
c
)
)
),
IFERROR(
VALUE(
t
),
t
)
)Excel solution 11 for Transpose the data as shown, proposed by Fredson Alves Pinho:
=LET(
c³,
--TEXTSPLIT(
TEXTJOIN(
", ",
0,
B2:B10
),
,
", "
),
c²,
TEXTSPLIT(
TEXTJOIN(
", ",
0,
A2:A10
),
,
", "
),
c¹,
SCAN(
"a",
IF(
ISNUMBER(
c³
),
c³,
c²
),
LAMBDA(
a,
v,
IF(
ISNUMBER(
v
),
a,
v
)
)
),
PIVOTBY(
c¹,
c²,
c³,
SUM,
,
0,
,
0,
,
ISNUMBER(
c³
)
)
)Excel solution 12 for Transpose the data as shown, proposed by red craven:
=LET(
a,
A2:A10,
b,
B2:B10,
F,
LAMBDA(
x,
TEXTSPLIT(
ARRAYTOTEXT(
FILTER(
x,
b>0
)
),
,
", "
)
),
c,
TOCOL(
IF(
WRAPROWS(
F(
a
),
3
)>0,
FILTER(
a,
b=0
)
)
),
p,
DROP(
PIVOTBY(
HSTACK(
XMATCH(
c,
c
),
c
),
F(
a
),
--F(
b
),
SUM,
,
0,
,
0
),
,
1
),
IF(
TAKE(
p,
,
1
)&p="",
"Country",
p
)
)
=LET(
F,
LAMBDA(
x,
TEXTSPLIT(
ARRAYTOTEXT(
x
),
,
", "
)
),
a,
F(
A2:A10
),
b,
F(
B2:B10
),
s,
SCAN(
0,
b="",
SUM
),
p,
DROP(
PIVOTBY(
HSTACK(
s,
XLOOKUP(
s,
s,
a
)
),
a,
--b,
SUM,
,
0,
,
0,
,
b>""
),
,
1
),
IF(
TAKE(
p,
,
1
)&p="",
"Country",
p
)
)Excel solution 13 for Transpose the data as shown, proposed by Ricardo Romero Garcia:
=LET(
fx,
LAMBDA(
c,
REDUCE(
,
c,
LAMBDA(
a,
v,
VSTACK(
a,
SI(
ISNUMBER(
SEARCH(
",",
v
)
),
TOCOL(
TEXTSPLIT(
v,
", "
)
),
v
)
)
)
)
),
b,
fx(
A2:A10
),
d,
SCAN(
"",
b,
LAMBDA(
a,
v,
SI(
LEN(
v
)>1,
v,
a
)
)
),
e,
--fx(
B2:B10
),
DROP(
PIVOTBY(
d,
b,
e,
SUM,
,
0,
2,
,
,
e
),
,
-1
)
)Excel solution 14 for Transpose the data as shown, proposed by Manuel Reyes:
=LET(
EJE,
TOCOL(
TEXTSPLIT(
TEXTJOIN(
"/",
0,
A2:B10
),
", ",
"/"
),
3
),
CONSE,
SCAN(
0,
EJE,
LAMBDA(
a,
v,
IF(
LEN(
v
)>4,
a+1,
SUM(
a
)
)
)
),
CONSO1,
TOCOL(
IF(
ISNUMBER(
--EJE
),
CONSE,
nume
),
2
),
CONSE2,
SCAN(
"",
EJE,
LAMBDA(
a,
v,
IF(
LEN(
v
)>4,
v,
a
)
)
),
CONSO2,
TOCOL(
IF(
ISNUMBER(
--EJE
),
CONSE2,
nume
),
2
),
CONSO3,
TOCOL(
IF(
CODE(
EJE
)>96,
EJE,
nume
),
2
),
CONSO4,
TOCOL(
IF(
ISNUMBER(
--EJE
),
EJE,
nume
),
2
),
DROP(
PIVOTBY(
HSTACK(
CONSO1,
CONSO2
),
CONSO3,
--CONSO4,
SUM,
0,
0,
,
0
),
,
1
)
)Solving the challenge of Transpose the data as shown with Python
_x000D_Python solution 1 for Transpose the data as shown, proposed by Luan Rodrigues:
import pandas as pd
import numpy as np
file = r"PQ_Challenge_277.xlsx"
df = pd.read_excel(file,usecols="A:B")
df = df.assign(
Data1=np.where(df['Data2'].isnull(), 'Country', df['Data1']),
Data2=np.where(df['Data2'].isnull(), df['Data1'], df['Data2'])
)[['Data1','Data2']]
df['Country'] = np.where(df['Data1'] == 'Country', df['Data2'], np.nan)
df['Country'] = df['Country'].ffill()
df = df[df['Data1'] != 'Country']
def transform(group):
val1 = ', '.join(map(str, group['Data1'])).split(', ')
val2 = ', '.join(map(str, group['Data2'])).split(', ')
zipped = list(zip(val1, val2))
df_result = pd.DataFrame(zipped, columns=['Data1', 'Data2'])
df_result['Country'] = group['Country'].iloc[0]
return df_result
df_final = df.groupby('Country').apply(transform).reset_index(drop=True)
df_final = df_final.pivot(index='Country', columns='Data1', values='Data2').reset_index()
df_final = df_final.rename_axis(None, axis=1)
print(df_final)
Solving the challenge of Transpose the data as shown with Python in Excel
_x000D_Python in Excel solution 1 for Transpose the data as shown, proposed by Alejandro Campos:
df = xl("A1:B10", headers=True).astype(str).fillna('')
d, c = {'Country': [], 'a': [], 'b': [], 'c': [], 'd': []}, None
for _, r in df.iterrows():
if r['Data1'] in ['Denmark', 'Sweden', 'Germany']:
c = r['Data1']
d['Country'].append(c)
[d[k].append('') for k in ['a', 'b', 'c', 'd']]
else:
for k, v in zip(r['Data1'].split(', '), r['Data2'].split(', ')): d[k][-1] = v
transposed_df = pd.DataFrame(d)
Python in Excel solution 2 for Transpose the data as shown, proposed by Francesco Bianchi 🇮🇹:
df = xl("A1:B10", headers=True)
df['Country'] = [x if pd.isna(y) else np.nan for x, y in zip(df['Data1'], df['Data2'])]
df['Country'] = df['Country'].ffill()
df['a'] = [x if not pd.isna(y) else np.nan for x, y in zip(df['Data1'].shift(-1), df['Country'])]
df['v'] = [x if not pd.isna(y) else np.nan for x, y in zip(df['Data2'].shift(-1), df['Country'])]
df = df.iloc[:,2:].dropna()
df['a'] = df['a'].str.split(', ')
df['v'] = df['v'].astype(str).str.split(', ')
df = df.explode(['a', 'v'], ignore_index=True)
df['v'] = df['v'].astype(float)
pivot_df = df.pivot(index='Country', columns='a', values='v')
pivot_df.fillna('', inplace=True)
pivot_df = pivot_df.reset_index(drop=False)
pivot_df.columns.name = None
pivot_df['Country'] = pd.Categorical(pivot_df['Country'], categories=df['Country'].unique(), ordered=True)
pivot_df = pivot_df.sort_values('Country').reset_index(drop=True)
pivot_df
Solving the challenge of Transpose the data as shown with R
_x000D_R solution 1 for Transpose the data as shown, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_277.xlsx"
input = read_excel(path, range = "A1:B10")
test = read_excel(path, range = "D1:H4")
result = input %>%
mutate(country = ifelse(is.na(Data2), Data1, NA)) %>%
fill(country) %>%
na.omit() %>%
separate_rows(c(Data1, Data2)) %>%
mutate(Data2 = as.numeric(Data2)) %>%
pivot_wider(names_from = Data1, values_from = Data2)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE