In the question table, texts are provided for different groups. Divide all the texts of each group into two columns respectively. For example, in group C, texts t9 and t10 are available. So t9 is provided in column 1 and t10 is provided in column 2 in result table.
📌 Challenge Details and Links
Challenge Number: 94
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Two-Column Text! with Power Query
Power Query solution 1 for Two-Column Text!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromList(
List.Combine(
Table.Group(
Source,
"Group",
{
"R",
each
let
T = Table.ToColumns(_)
in
List.Transform(List.Split(T{1}, 2), each {T{0}{0}} & _)
}
)[R]
),
each _,
{"Group", "Column 1", "Column 2"}
)
in
SPower Query solution 2 for Two-Column Text!, proposed by Zoran Milokanović:
let
Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
S = Table.FromRows(
List.TransformMany(
List.Distinct(Source{0}),
each
let
f = (p) => List.PositionOf(Source{0}, _, p)
in
List.Split(List.Range(Source{1}, f(0), f(1) - f(0) + 1), 2),
(i, _) => {i} & List.FirstN(_ & {null}, 2)
),
{"Group", "Column 1", "Column 2"}
)
in
SPower Query solution 3 for Two-Column Text!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromColumns(
List.Zip(
List.Combine(
Table.Group(
Source,
"Group",
{
"R",
each
let
T = Table.ToColumns(_)
in
List.Transform(List.Split(T{1}, 2), each {T{0}{0}} & _)
}
)[R]
)
),
{"Group", "Column 1", "Column 2"}
)
in
SPower Query solution 4 for Two-Column Text!, proposed by Pavel Adam:
let
outputColumnCount = 2,
customGroup = Table.Group(
input,
{"Group"},
{
{
"AllRows",
each [
subList = _[Text],
actGroup = _[Group]{0},
prepList = List.Split(subList, outputColumnCount),
transformedList = List.Transform(
prepList,
(actRow) =>
[
listSize = List.Count(actRow),
out1 =
if listSize = outputColumnCount then
actRow
else
actRow & List.Repeat({null}, outputColumnCount - listSize)
][out1]
),
out = Table.AddColumn('#'table(null, transformedList), "Group", each actGroup, type text)
][out]
}
}
),
finalOut = Table.Combine(customGroup[AllRows])
in
finalOutPower Query solution 5 for Two-Column Text!, proposed by 🇵🇪 Ned Navarrete C.:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Group"},
{{"X",
each [a=List.Split(
[Text],
2
),
b =Table.FromColumns(
a
),
c=Table.Transpose(
b
)][c]}}
),
Expand = Table.ExpandTableColumn(
Grouped,
"X",
{"Column1",
"Column2"}
)
in
ExpandPower Query solution 6 for Two-Column Text!, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.ExpandTableColumn(
Table.Group(Source, {"Group"}, {{"All", each Table.AddIndexColumn(_, "Index", 0, 1)}}),
"All",
{"Text", "Index"},
{"Text", "Index"}
),
AddModulo = Table.AddColumn(
Group,
"Modulo",
each "Column " & Text.From(Number.Mod([Index], 2) + 1)
),
Pivot = Table.RemoveColumns(
Table.Pivot(AddModulo, List.Distinct(AddModulo[Modulo]), "Modulo", "Text"),
"Index"
),
PartitionPiv = Table.Partition(
Pivot,
"Group",
List.Count(List.Distinct(Pivot[Group])),
each List.PositionOf(List.Distinct(Pivot[Group]), _)
),
CleanNulls = List.Transform(
List.Transform(PartitionPiv, each List.Transform(Table.ToColumns(_), each List.RemoveNulls(_))),
each Table.SelectRows(Table.FromColumns(_), each [Column2] <> null)
),
ToTable = Table.ExpandTableColumn(
Table.FromList(CleanNulls, Splitter.SplitByNothing(), {"Z"}),
"Z",
{"Column1", "Column2", "Column3"},
{"Group", "Column1", "Column2"}
)
in
ToTablePower Query solution 7 for Two-Column Text!, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData"]}[Content],
NbCol = 2,
CN = {"Group"} & List.Transform({1 .. NbCol}, each "Column " & Text.From(_)),
Group = Table.Group(
Source,
"Group",
{
"G",
each
let
_G = [Group]{0}
in
List.Transform(
List.Split([Text], NbCol),
each {_G} & _ & List.Repeat({null}, NbCol - List.Count(_))
)
}
),
Result = Table.FromRows(List.Combine(Group[G]), CN)
in
ResultPower Query solution 8 for Two-Column Text!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.Group(S,{"Group"},{"G", each Table.FromColumns(List.Zip(List.Split([Text],2)))}),
Sol = Table.ExpandTableColumn(a,"G",{"Column1","Column2"},{"Column 1","Column 2"})
in
SolPower Query solution 9 for Two-Column Text!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Group",
{"A", each [S = List.Split([Text], 2), T = Table.FromList(S, each _, {"Text1", "Text2"})][T]}
),
Return = Table.ExpandTableColumn(Group, "A", {"Text1", "Text2"})
in
ReturnPower Query solution 10 for Two-Column Text!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Group"}, {{"All", each
Table.FromColumns(List.Zip(List.Split([Text],2)))}}),
Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
SolPower Query solution 11 for Two-Column Text!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Таблиця1"]}[Content],
Group = Table.Group(
Source,
{"Group"},
{
{
"x",
each Table.FromColumns({List.Alternate(_[Text], 1, 1, 1), List.Alternate(_[Text], 1, 1, 0)})
}
}
),
Expand = Table.ExpandTableColumn(Group, "x", {"Column1", "Column2"}, {"Column1", "Column2"})
in
ExpandPower Query solution 12 for Two-Column Text!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.TransformColumnTypes(Source, {{"Group", type text}, {"Text", type text}}),
B = Table.Group(
A,
{"Group"},
{{"tbl", each _, type table [Group = nullable text, Text = nullable text]}}
),
F = (x) =>
let
b = Table.AddIndexColumn(x, "Index", 1, 1, Int64.Type),
c = Table.AddColumn(
b,
"Column",
each if Number.Mod([Index], 2) = 1 then "Column1" else "Column2"
),
d = Table.AddColumn(c, "N", each if [Column] = "Column1" then [Index] else null),
e = Table.FillDown(d, {"N"}),
f = Table.RemoveColumns(e, {"Index"}),
g = Table.Pivot(f, List.Distinct(f[Column]), "Column", "Text"),
h = Table.RemoveColumns(g, {"N"})
in
h,
C = Table.AddColumn(B, "F", each F([tbl])),
D = Table.SelectColumns(C, {"F"}),
E = Table.ExpandTableColumn(
D,
"F",
{"Group", "Column1", "Column2"},
{"Group", "Column1", "Column2"}
)
in
ESolving the challenge of Two-Column Text! with Excel
Excel solution 1 for Two-Column Text!, proposed by محمد حلمي:
=REDUCE(
H2:J2,
UNIQUE(
B3:B13
),
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
HSTACK(
v,
WRAPROWS(
FILTER(
C3:C13,
B3:B13=v
),
2,
""
)
)
),
v
)
)
)Excel solution 2 for Two-Column Text!, proposed by محمد حلمي:
=REDUCE(
HSTACK(
B2,
"Column "&{1,
2}
), UNIQUE(
B3:B13
),
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
HSTACK(
v,
WRAPROWS(
FILTER(
C3:C13,
B3:B13=v
),
2,
""
)
)
),
v
)
)
)Excel solution 3 for Two-Column Text!, proposed by محمد حلمي:
=REDUCE(H2:J2,
B3:B13,
LAMBDA(a,
v,
LET(
e,
OFFSET(
v,
,
1
),
IFNA(UNIQUE(VSTACK(a,
IF((v=OFFSET(
v,
1, ))*
ISODD(
COUNTIF(
v:B3,
v
)
),
HSTACK(
v,
e,
OFFSET(
v,
1,
1
)
),IF(
OR(
e=TAKE(
a,
-1
)
),
a,
HSTACK(
v,
e
)
)))),
""))))Excel solution 4 for Two-Column Text!, proposed by 🇵🇪 Ned Navarrete C.:
=REDUCE(
H2:J2,
UNIQUE(
B3:B13
),
LAMBDA(
c,
v,
IFNA(
VSTACK(
c,
IFNA(
HSTACK(
v,
WRAPROWS(
FILTER(
C3:C13,
B3:B13=v
),
2,
""
)
),
v
)
),
""
)
)
)Excel solution 5 for Two-Column Text!, proposed by Oscar Mendez Roca Farell:
=REDUCE(
HSTACK(
B2,
"Column"&{1,
2}
),
UNIQUE(
B3:B13
),
LAMBDA(
i,
x,
LET(
w,
WRAPROWS(
FILTER(
C3:C13,
B3:B13=x
),
2
),
IFNA(
VSTACK(
i,
HSTACK(
REPT(
x,
SEQUENCE(
ROWS(
w
)
)^0
),
w
)
),
""
)
)
)
)Excel solution 6 for Two-Column Text!, proposed by Julian Poeltl:
=LET(
G,
B3:B13,
T,
C3:C13,
U,
UNIQUE(
G
),
R,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
U
)
),
LAMBDA(
A,
B,
VSTACK(
A,
WRAPROWS(
FILTER(
T,
G=INDEX(
U,
B
)
),
2,
""
)
)
)
),
1
),
""
),
VSTACK(
HSTACK(
"Group",
"Column 1",
"Column 2"
),
HSTACK(
XLOOKUP(
TAKE(
R,
,
1
),
T,
G
),
R
)
)
)Excel solution 7 for Two-Column Text!, proposed by Kris Jaganah:
=LET(
a,
B3:B13,
b,
C3:C13,
c,
SEQUENCE(
ROWS(
a
)
)-XMATCH(
a,
a
)+1,
d,
ROUNDUP(
c/2,
0
),
e,
MOD(
c-1,
2
)+1,
f,
DROP(
PIVOTBY(
a&d,
e,
b,
CONCAT,
0,
0,
,
0
),
1
),
VSTACK(
{"Group",
"Column 1",
"Column 2"},
HSTACK(
LEFT(
TAKE(
f,
,
1
)
),
DROP(
f,
,
1
)
)
)
)Excel solution 8 for Two-Column Text!, proposed by Imam Hambali:
=LET( g,
B3:B13, ug,
UNIQUE(
g
), t,
C3:C13, a,
HSTACK(
ug,
IF(
MOD(
COUNTIFS(
g,
ug
),
2
)=1,
"",
NA()
)
), b,
WRAPROWS(
TOCOL(
TAKE(
SORT(
VSTACK(
HSTACK(
g,
t
),
a
),
1
),
,
-1
),
3
),
2
), HSTACK(
XLOOKUP(
TAKE(
b,
,
1
),
t,
g
),
b
))Excel solution 9 for Two-Column Text!, proposed by Sunny Baggu:
=IFNA( REDUCE( {"Group",
"Column 1",
"Column 2"}, UNIQUE(
B3:B13
), LAMBDA(
a,
v,
VSTACK(
a,
IFNA(
HSTACK(
v,
WRAPROWS(
FILTER(
C3:C13,
B3:B13 = v
),
2,
""
)
),
v
)
)
) ), "")Excel solution 10 for Two-Column Text!, proposed by Andy Heybruch:
=LET( _groups,
UNIQUE(
B3:B13
), _text,
B3:B13&"|"&C3:C13, _split,
DROP(
IFERROR(
REDUCE(
"",
_groups,
LAMBDA(
a,
v,
VSTACK(
a,
WRAPROWS(
FILTER(
_text,
LEFT(
_text
)=v
),
2
)
)
)
),
""
),
1
), IFERROR(
HSTACK(
TEXTBEFORE(
TAKE(
_split,
,
1
),
"|"
),
TEXTAFTER(
TAKE(
_split,
,
1
),
"|"
),
TEXTAFTER(
TAKE(
_split,
,
-1
),
"|"
)
),
""
)
)Excel solution 11 for Two-Column Text!, proposed by Ankur Sharma:
=LET(
a,
B3:B13,
b,
UNIQUE(
a
),
c,
a & "@" & C3:C13,
d,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
"$",
,
MAP(
b,
LAMBDA(
z,
TEXTJOIN(
"$",
FALSE,
WRAPROWS(
FILTER(
c,
a = z
),
2,
""
)
)
)
)
),
,
"$"
),
2,
""
),
HSTACK(
TAKE(
TEXTBEFORE(
d,
"@"
),
,
1
),
TEXTAFTER(
d,
"@",
,
,
,
""
)
)
)Excel solution 12 for Two-Column Text!, proposed by Asheesh Pahwa:
=IFNA(
DROP(
REDUCE(
"",
UNIQUE(
B3:B13
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:C13,
B3:B13=y
),
w,
WRAPROWS(
f,
2,
""
),
IFNA(
HSTACK(
y,
w
),
y
)
)
)
)
),
1
),
""
)Excel solution 13 for Two-Column Text!, proposed by Bilal Mahmoud kh.:
=IFNA(
REDUCE(
{"Group",
"C1",
"C2"},
UNIQUE(
B3:B13
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
TEXTSPLIT(
REPT(
y&",",
ROUNDUP(
COUNTA(
FILTER(
B3:B13,
B3:B13=y
)
)/2,
0
)
),
,
",",
TRUE
),
WRAPROWS(
FILTER(
C3:C13,
B3:B13=y
),
2
)
)
)
)
),
""
)Excel solution 14 for Two-Column Text!, proposed by Eddy Wijaya:
=LET( init,
B3:B13, counter,
ROUNDUP(
MAP(
UNIQUE(
init
),
LAMBDA(
m,
ROWS(
FILTER(
init,
init=m
)
)
)
)/2,
0
), adjGroup,
REPT(
UNIQUE(
init
)&",",
counter
), REDUCE(
H2:J2,
adjGroup,
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
LET(
split,
TEXTSPLIT(
v,
,
",",
TRUE
),
val,
WRAPROWS(
FILTER(
C3:C13,
init=UNIQUE(
split
)
),
2,
""
),
HSTACK(
split,
val
)
)
),
""
)
)
)
)Excel solution 15 for Two-Column Text!, proposed by El Badlis Mohd Marzudin:
=LET(
d,
B3:B13,
DROP(
IFNA(
REDUCE(
"",
UNIQUE(
d
),
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
WRAPROWS(
FILTER(
C3:C13,
d=w
),
2
),
b,
EXPAND(
w,
ROWS(
a
),
,
w
),
HSTACK(
b,
a
)
)
)
)
),
""
),
1
)
)Excel solution 16 for Two-Column Text!, proposed by ferhat CK:
=LET(
gr,
B3:B13,
tx,
C3:C13,
a,
UNIQUE(
gr
),
d,
WRAPROWS(
DROP(
REDUCE(
TOCOL(
WRAPROWS(
FILTER(
tx,
gr="a"
),
2,
""
)
),
a,
LAMBDA(
x,
y,
VSTACK(
x,
TOCOL(
WRAPROWS(
FILTER(
tx,
gr=y
),
2,
""
)
)
)
)
),
4
),
2,
""
),
HSTACK(
XLOOKUP(
CHOOSECOLS(
d,
1
),
tx,
gr
),
d
)
)Excel solution 17 for Two-Column Text!, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
MAP(
UNIQUE(
B3:B13
),
LAMBDA(
x,
SUBSTITUTE(
"|"&TEXTJOIN(
{"/",
"|"},
,
FILTER(
C3:C13,
B3:B13=x
)
),
"|",
"|"&x&"/"
)
)
)
),
"/",
"|",
1,
,
""
)