Transpose the given table as show. Hence, As in one column, Bs in one column….
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 268
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Transpose table: As in one with Power Query
Power Query solution 1 for Transpose table: As in one, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tab1 = List.Combine(Table.ToColumns(Source)),
Tab2 = List.Transform(
{"A" .. "F"},
(x) => List.Transform({"1" .. "6"}, (y) => if List.Contains(Tab1, x & y) then x & y else null)
),
Result = Table.FromColumns(Tab2, List.Transform({"1" .. "6"}, each "Col" & _))
in
ResultPower Query solution 2 for Transpose table: As in one, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData268"]}[Content],
T = Table.FromRows(
List.Transform(
List.RemoveNulls(List.Combine(Table.ToColumns(Source))),
each {_, Text.Start(_, 1), Text.End(_, 1)}
),
{"V", "L", "C"}
),
Group = Table.Group(
T,
"C",
{
"G",
each Table.FromRecords(
{
List.Accumulate(
Table.ToRecords(_),
[],
(s, c) =>
Record.AddField(s, "Col" & Text.From(List.PositionOf({"A" .. "Z"}, c[L])), c[V])
)
}
)
}
),
Combine = Table.Combine(Group[G])
in
CombinePower Query solution 3 for Transpose table: As in one, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lst = Table.UnpivotOtherColumns(Source, {}, "A", "V")[V],
Txt = List.Sort(List.Distinct(List.Transform(Lst, each Text.Start(_, 1)))),
Num = List.Distinct(List.Transform(Lst, each Text.Middle(_, 1, 9))),
Result = Table.TransformColumnNames(
Table.FromColumns(
List.Split(
List.TransformMany(
Txt,
each Num,
(x, y) => try Lst{List.PositionOf(Lst, x & Text.From(y))} otherwise null
),
List.Count(Num)
)
),
each Text.RemoveRange(_, 3, 3)
)
in
ResultPower Query solution 4 for Transpose table: As in one, proposed by Antriksh Sharma:
let
Source = Table,
A = Table.SelectRows(
Table.FromColumns({List.Combine(Table.ToColumns(Source))}, {"C"}),
each [C] <> ""
),
B = Table.FromColumns({List.Distinct(List.Transform(A[C], each Text.Start(_, 1)))}, {"Char"}),
C = List.Accumulate(
{1 .. Table.RowCount(B)},
B,
(s, c) =>
Table.AddColumn(
s,
Text.From(c),
(x) => List.First(Table.SelectRows(A, each [C] = x[Char] & Text.From(c))[C])
)
),
D = Table.Transpose(Table.RemoveColumns(C, {"Char"}))
in
DPower Query solution 5 for Transpose table: As in one, proposed by Peter Krkos:
let F = (z)=> Number.From(Text.At(z, 1)) in Value.Compare( List.Max({F(y[Col1]), F(y[Col2])}), List.Max({F(x[Col1]), F(x[Col2])})))[T])
Power Query solution 6 for Transpose table: As in one, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = [
lt = List.Transform,
a = List.Select(List.Combine(Table.ToColumns(Source)), each _ <> null),
nr = {"0" .. "9"},
abc = {"A" .. "Z"},
b = Table.FromColumns(
{a}
& lt(
{nr, abc},
(x) =>
lt(
a,
(y) =>
let
b1 = Text.Remove(y, x)
in
if Character.ToNumber(b1) > 64 then
"Col" & Text.From(Character.ToNumber(b1) - 64)
else
b1
)
)
)
][b],
pivot = Table.RemoveColumns(
Table.Pivot(Custom1, List.Distinct(Custom1[Column2]), "Column2", "Column1"),
"Column3"
)
in
pivotPower Query solution 7 for Transpose table: As in one, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.FromColumns({List.RemoveNulls(List.Combine(Table.ToColumns(Source)))}, {"tmp"}),
B = Table.AddColumn(A, "N", each Text.Select([tmp], {"0" .. "9"})),
C = Table.AddColumn(
B,
"C",
each List.Transform(
{Text.Remove([tmp], {"0" .. "9"})},
(x) => "Col" & Text.From(List.PositionOf({"A" .. "Z"}, x) + 1)
){0}
),
Result = Table.RemoveColumns(Table.Pivot(C, List.Distinct(C[C]), "C", "tmp"), "N")
in
ResultPower Query solution 8 for Transpose table: As in one, proposed by Khanh Lam chi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
UP = Table.UnpivotOtherColumns(Source, {}, "A", "V"),
AD = Table.AddColumn(UP, "N", each Number.From(Text.Middle([V], 1))),
TX = List.Sort(List.Distinct(Table.AddColumn(AD, "TX", each Text.Start([V], 1))[TX])),
M = List.Max(AD[N]),
FM = List.Transform({1 .. M}, Text.From),
MX = List.TransformMany(TX, each FM, (x, y) => x & y),
RS = Table.FromColumns(
List.Split(List.Transform(MX, each if List.Contains(UP[V], _) then _ else null), M),
List.Transform(FM, each "Col" & _)
)
in
RSSolving the challenge of Transpose table: As in one with Excel
Excel solution 1 for Transpose table: As in one, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
TOCOL(
A2:B15,
3
),
DROP(
PIVOTBY(
--MID(
z,
2,
2
),
LEFT(
z
),
z,
SINGLE,
,
0,
,
0
),
1,
1
)
)Excel solution 2 for Transpose table: As in one, proposed by Rick Rothstein:
=LET(
x,
{"A",
"B",
"C",
"D",
"E",
"F"}&SEQUENCE(
6
),
IF(
COUNTIF(
A2:B15,
x
),
x,
""
)
)
Otherwise this general solution...
=LET(r,
A2:B15,
x,
SORT(
UNIQUE(
LEFT(
TOROW(
r,
1,
1
)
),
1
),
,
,
1
)&SEQUENCE(MAX(0+(0&MID(
r,
2,
9
)))),
IF(
COUNTIF(
r,
x
),
x,
""
))Excel solution 3 for Transpose table: As in one, proposed by 🇰🇷 Taeyong Shin:
=LET(d,
A2:B15,
r,
SEQUENCE(MAX(--(0&RIGHT(
d
)))),
a,
CHAR(
TOROW(
r
)+64
)&r,
REPT(
a,
COUNTIF(
d,
a
)
))Excel solution 4 for Transpose table: As in one, proposed by Oscar Mendez Roca Farell:
=LET(
d,
TOCOL(
A2:B15,
1
),
DROP(
PIVOTBY(
MID(
d,
2,
1
),
MID(
d,
1,
1
),
d,
SINGLE,
,
0,
,
0
),
,
1
)
)Excel solution 5 for Transpose table: As in one, proposed by Duy Tùng:
=LET(
a,
TOCOL(
A2:B15,
1
),
DROP(
PIVOTBY(
RIGHT(
a
),
"Col"&CODE(
LEFT(
a
)
)-64,
a,
SINGLE,
,
0,
,
0
),
,
1
)
)Excel solution 6 for Transpose table: As in one, proposed by Sunny Baggu:
=LET(
_a,
TOCOL(
A2:B15,
3
),
_b,
SORT(
UNIQUE(
LEFT(
_a
)
)
),
_c,
UNIQUE(
RIGHT(
_a
)
),
_d,
TOROW(
_b
) & _c,
IF(
MAP(
_d,
LAMBDA(
a,
OR(
_a = a
)
)
),
_d,
""
)
)Excel solution 7 for Transpose table: As in one, proposed by LEONARD OCHEA 🇷🇴:
=LET(
d,
TOCOL(
A2:B15,
1
),
DROP(
PIVOTBY(
RIGHT(
d
),
"Col"&CODE(
LEFT(
d
)
)-64,
d,
SINGLE,
,
0,
,
0
),
,
1
)
)Excel solution 8 for Transpose table: As in one, proposed by Md. Zohurul Islam:
=LET(
u,
TOCOL(
A2:B15,
3
),
v,
--MID(
u,
2,
99
),
w,
TOROW(
SORT(
UNIQUE(
LEFT(
u,
1
)
)
)
),
s,
UNIQUE(
v
),
z,
REDUCE(
"Col"&TOROW(
s
),
s,
LAMBDA(
x,
y,
LET(
a,
TOROW(
FILTER(
u,
v=y
)
),
b,
IF(
ISERROR(
XMATCH(
w&y,
a
)
),
"",
w&y
),
VSTACK(
x,
b
)
)
)
),
z
)Excel solution 9 for Transpose table: As in one, proposed by Pieter de B.:
=LET(
a,
TOCOL(
A2:B15,
1
),
DROP(
PIVOTBY(
RIGHT(
a
),
"Col"&CODE(
a
)-64,
a,
SINGLE,
,
0,
,
0
),
,
1
)
)Excel solution 10 for Transpose table: As in one, proposed by Ankur Sharma:
=LET(
r,
TOCOL(
A2:B15,
3
),
a,
SORT(
UNIQUE(
LEFT(
r,
1
)
)
),
n,
SEQUENCE(
1,
MAX(
--RIGHT(
r,
1
)
)
),
r_2,
TOCOL(
a & n
),
WRAPCOLS(
IF(
ISNUMBER(
XMATCH(
r_2,
r
)
),
r_2,
""
),
MAX(
n
)
)
)Excel solution 11 for Transpose table: As in one, proposed by Meganathan Elumalai:
=LET(
a,
TOCOL(
A2:B15,
1
),
b,
TOROW(
SORT(
UNIQUE(
LEFT(
a
)
)
)
),
c,
UNIQUE(
MID(
a,
2,
9
)
),
XLOOKUP(
b&c,
a,
a,
""
)
)Excel solution 12 for Transpose table: As in one, proposed by Eddy Wijaya:
=LET(
t,
A2:B15,
a,
CHAR(
SEQUENCE(
27,
,
65
)
),
arr,
TOCOL(
t,
1
),
atv,
LEFT(
arr,
1
),
s,
SUBSTITUTE(
arr,
atv,
XMATCH(
atv,
a,
0
)
),
mx,
MAX(
--LEFT(
s,
1
),
--RIGHT(
s,
1
)
),
IFERROR(
MAP(
MAKEARRAY(
mx,
mx,
LAMBDA(
r,
c,
c&r
)
),
LAMBDA(
m,
XLOOKUP(
m,
s,
arr
)
)
),
""
)
)Excel solution 13 for Transpose table: As in one, proposed by Ricardo Romero Garcia:
=EXCLUIR(
LET(
a;
ORDENAR(
ENCOL(
A2:B15;
1
)
);
b;
DERECHA(
a
);
c;
"Col"&CODIGO(
IZQUIERDA(
a
)
)-64;
PIVOTARPOR(
b;
c;
a;
MATRIZATEXTO;
;
0;
;
0
)
);
;
1
)Solving the challenge of Transpose table: As in one with Python
Python solution 1 for Transpose table: As in one, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_268.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=15)
test = pd.read_excel(path, usecols="E:J", nrows=6).fillna("").rename(columns=lambda col: col.split('.')[0])
input['row'] = np.arange(len(input))
melted = input.melt(id_vars="row", value_name="value").dropna(subset=["value"])
melted["value1"] = melted["value"]
splits = melted["value"].str.extract(r"^([^d]+)(.*)$")
melted["name"] = splits[0]
melted["value"] = splits[1]
result = melted.pivot_table(index="&value", columns="name", values="value1", aggfunc="first")
result = result.reindex(sorted(result.columns), axis=1)
result = result.fillna("").reset_index(drop=True)
result.columns = test.columns
print(result.equals(test)) # True
Solving the challenge of Transpose table: As in one with Python in Excel
Python in Excel solution 1 for Transpose table: As in one, proposed by Alejandro Campos:
df = xl("A1:B15", headers=True)
pivot_df = pd.melt(df, var_name='Category', value_name='Value').dropna()
pivot_df[['Letter', 'number']] = pivot_df['Value'].str.extract('([A-Z]+)(d+)')
pivot_df = pivot_df.pivot(index='number', columns='Letter', values='Value').reindex(sorted(pivot_df['Letter'].unique()), axis=1).fillna('')
pivot_df.columns = [f'Col{i+1}' for i in range(len(pivot_df.columns))]
pivot_df.reset_index(drop=True, inplace=True)
pivot_df
Solving the challenge of Transpose table: As in one with R
R solution 1 for Transpose table: As in one, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_268.xlsx"
input = read_excel(path, range = "A1:B15")
test = read_excel(path, range = "E1:J7") %>%
replace(is.na(.), "")
result <- input %>%
pivot_longer(everything(), values_to = "value", names_to = NULL) %>%
drop_na() %>%
mutate(value1 = value) %>%
separate(value, into = c("name", "value"), sep = "(?<=\D)(?=\d)", remove = FALSE) %>%
pivot_wider(names_from = name, values_from = value1) %>%
select(-value) %>%
replace(is.na(.), "") %>%
select(sort(names(.)))
colnames(result) = colnames(test)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&