Prepare dictionary entries from Key Value pairs. If a key is vowel, then that key and its corresponding value both need to be omitted.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 357
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Build Dictionary Excluding Vowels with Power Query
Power Query solution 1 for Build Dictionary Excluding Vowels, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
P = Text.Split,
R = Table.AddColumn(S, "R", each
[d = ", ", k = P([Key], d), v = P([Value], d),
u = Text.Combine(List.Transform({0..List.Count(k) - 1}, each if Text.Contains("aeiou", k{_}) then null else k{_} & ":" & v{_}), d)][u]
)[[R]]
in
R
Blessings!
Power Query solution 2 for Build Dictionary Excluding Vowels, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Vowels = {"a", "e", "i", "o", "u"},
Return = Table.AddColumn(
Source,
"Answer",
each [
S1 = Text.Split([Key], ", "),
S2 = Text.Split([Value], ", "),
Z = List.Zip({S1, S2}),
F = List.Select(Z, (f) => not List.Contains(Vowels, f{0})),
T = List.Transform(F, (f) => Text.Combine(f, ":")),
R = Text.Combine(T, ", ")
][R]
)
in
ReturnPower Query solution 3 for Build Dictionary Excluding Vowels, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
m = Text.Split([Key], ", "),
n = Text.Split([Value], ", "),
a = List.Zip({m, n}),
b = List.Transform(a, each _{0} & ":" & _{1}),
c = List.Select(
b,
each List.AllTrue(List.Transform(Text.ToList("aeiou"), (x) => not Text.Contains(_, x)))
)
in
Text.Combine(c, ", ")
)[[Answer]]
in
SolPower Query solution 4 for Build Dictionary Excluding Vowels, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lists = Table.TransformColumns(
Source,
List.Transform({"Key", "Value"}, each {_, each Text.Split(_, ", ")})
),
Sol = Table.AddColumn(
Lists,
"Answer",
each
let
a = List.Zip({[Key], [Value]}),
b = List.Transform(a, each _{0} & ":" & _{1}),
c = List.Select(
b,
each List.AllTrue(List.Transform(Text.ToList("aeiou"), (x) => not Text.Contains(_, x)))
)
in
Text.Combine(c, ", ")
)[[Answer]]
in
SolPower Query solution 5 for Build Dictionary Excluding Vowels, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x, y) =>
let
l = x,
n = y,
a = Text.Split(l, ", "),
b = Text.Split(n, ", "),
c = List.Zip({a, b}),
d = List.Select(c, each not List.ContainsAny(_, {"a", "e", "i", "o", "u"})),
e = List.Transform(d, each Text.Combine(_, ":")),
f = Text.Combine(e, ", ")
in
f,
Sol = Table.AddColumn(Origen, "Answer Expected", each Fx([Key], [Value]))
in
SolSolving the challenge of Build Dictionary Excluding Vowels with Excel
Excel solution 1 for Build Dictionary Excluding Vowels, proposed by Rick Rothstein:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
d,
", ",
t,
TEXTSPLIT(
x,
d
)&":"&TEXTSPLIT(
y,
d
),
TEXTJOIN(
d,
,
IF(
ISNUMBER(
FIND(
LEFT(
t
),
"aeiou"
)
),
"",
t
)
)
)
)
)Excel solution 2 for Build Dictionary Excluding Vowels, proposed by John V.:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
d,
", ",
k,
TEXTSPLIT(
a,
d
),
TEXTJOIN(
d,
,
REPT(
k&":"&TEXTSPLIT(
b,
d
),
ISERR(
FIND(
k,
"aeiou"
)
)
)
)
)
)
)Excel solution 3 for Build Dictionary Excluding Vowels, proposed by محمد حلمي:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
i,
TEXTSPLIT(
a,
", "
),
TEXTJOIN(
", ",
,
REPT(
i&":"&TEXTSPLIT(
b,
", "
),
ISERR(
FIND(
i,
"aeiou"
)
)
)
)
)
)
)Excel solution 4 for Build Dictionary Excluding Vowels, proposed by Kris Jaganah:
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,TEXTSPLIT(x&", "&y,,", "),b,WRAPCOLS(a,ROWS(a)/2),TEXTJOIN({":",", "},,FILTER(b,ISERR(TAKE(FIND(b,"aeiou"),,1)),"")))))Excel solution 5 for Build Dictionary Excluding Vowels, proposed by Kris Jaganah:
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,TEXTSPLIT(x&"-"&y,", ","-"),TEXTJOIN({":",", "},,TOCOL(IF(ISERR(FIND(TAKE(a,1),"aeiou")),a,""),,1)))))Excel solution 6 for Build Dictionary Excluding Vowels, proposed by Julian Poeltl:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
K,
V,
LET(
KS,
TEXTSPLIT(
K,
", "
),
VS,
TEXTSPLIT(
V,
", "
),
VW,
BYCOL(
KS={"a",
"e",
"i",
"o",
"u"},
LAMBDA(
A,
SUM(
--A
)
)
),
F,
FILTER(
HSTACK(
TRANSPOSE(
KS
),
TRANSPOSE(
VS
)
),
TRANSPOSE(
VW=0
)
),
IFERROR(
TEXTJOIN(
", ",
,
BYROW(
F,
LAMBDA(
A,
CHOOSECOLS(
A,
1
)&":"&CHOOSECOLS(
A,
2
)
)
)
),
""
)
)
)
)Excel solution 7 for Build Dictionary Excluding Vowels, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
e,
TEXTSPLIT(
a,
,
", "
),
f,
TEXTSPLIT(
b,
,
", "
),
g,
IF(
ISNUMBER(
MATCH(
e,
{"a",
"e",
"i",
"o",
"u"},
0
)
),
"",
e&":"&f
),
TEXTJOIN(
", ",
,
g
)
)
)
)Excel solution 8 for Build Dictionary Excluding Vowels, proposed by Sunny Baggu:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
_k,
TEXTSPLIT(
a,
,
", "
),
_v,
TEXTSPLIT(
b,
,
", "
),
_cri,
MAP(
_k,
LAMBDA(
a,
NOT(
OR(
{"a"; "e"; "i"; "o"; "u"} = a
)
)
)
),
_f,
FILTER(
HSTACK(
_k,
_v
),
_cri,
""
),
ARRAYTOTEXT(
BYROW(
_f,
LAMBDA(
x,
TEXTJOIN(
":",
,
x
)
)
)
)
)
)
)Excel solution 9 for Build Dictionary Excluding Vowels, proposed by Abdallah Ally:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
u,
v,
LET(
a,
TEXTSPLIT(
u,
,
", "
),
b,
TEXTSPLIT(
v,
,
", "
),
c,
HSTACK(
a,
b
),
d,
FILTER(
c,
BYROW(
a,
LAMBDA(
x,
NOT(
OR(
x={"a",
"e",
"i",
"o",
"u"}
)
)
)
),
""
),
ARRAYTOTEXT(
BYROW(
d,
LAMBDA(
x,
TEXTJOIN(
":",
1,
x
)
)
)
)
)
)
)Excel solution 10 for Build Dictionary Excluding Vowels, proposed by Abdallah Ally:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
x,
,
", "
),
b,
TEXTSPLIT(
y,
,
", "
),
c,
a&":"&b,
ARRAYTOTEXT(
FILTER(
c,
IFERROR(
FIND(
LEFT(
c
),
"aeiou"
),
0
)=0,
""
)
)
)
)
)Excel solution 11 for Build Dictionary Excluding Vowels, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
a,
b,
LET(
k,
TEXTSPLIT(
a,
,
", "
),
v,
TEXTSPLIT(
b,
,
", "
),
c,
k&":"&v,
f,
ISERR(
SEARCH(
k,
"aeiou"
)
),
IFERROR(
ARRAYTOTEXT(
FILTER(
c,
f
)
),
""
)
)
)
)Excel solution 12 for Build Dictionary Excluding Vowels, proposed by Asheesh Pahwa:
=MAP(
F5:F10,
G5:G10,
LAMBDA(
x,
y,
LET(
c,
TEXTSPLIT(
x,
", "
),
d,
TEXTSPLIT(
y,
", "
),
e,
c&":"&d,
f,
ISERROR(
XMATCH(
c,
{"a",
"e",
"i",
"o",
"u"}
)
),
ARRAYTOTEXT(
FILTER(
e,
f,
""
)
)
)
)
)Excel solution 13 for Build Dictionary Excluding Vowels, proposed by Asheesh Pahwa:
=MAP(
F5:F10,
G5:G10,
LAMBDA(
x,
y,
LET(
c,
TEXTSPLIT(
x,
", "
),
d,
TEXTSPLIT(
y,
", "
),
e,
c&":"&d,
f,
ISERROR(
XMATCH(
c,
{"a",
"e",
"i",
"o",
"u"}
)
),
ARRAYTOTEXT(
FILTER(
e,
f,
""
)
)
)
)
)Excel solution 14 for Build Dictionary Excluding Vowels, proposed by Ankur Sharma:
=TEXTJOIN(", ", TRU&E, LET(a, TEXTSPLIT(A2, ", "),
b, TEXTSPLIT(B2, ", "),
c, XMATCH(a, {"a","e","i","o","u"}),
FILTER(a & ":" & b, IF(ISNUMBER(c), 0, 1), "")))Excel solution 15 for Build Dictionary Excluding Vowels, proposed by JvdV –:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
k,
v,
TEXTJOIN(
", ",
1,
INDEX(
REGEXREPLACE(
SPLIT(
k,
", "
)&":"&SPLIT(
v,
", "
),
"[aeiou].*",
)
)
)
)
)Excel solution 16 for Build Dictionary Excluding Vowels, proposed by Ziad A.:
=ARRAYFORMULA(BYROW(REGEXREPLACE(SPLIT(REGEXREPLACE(A2:A7,"[aeiou]",),", ",,)&":"&SPLIT(B2:B7,","),"^:(d+)?$",),LAMBDA(r,TEXTJOIN(", ",1,r))))Excel solution 17 for Build Dictionary Excluding Vowels, proposed by Giorgi Goderdzishvili:
=
MAP(
A2:A7,
B2:B7,
LAMBDA(
_k,
_v,
LET(
_vow,
{"a",
"e",
"i",
"o",
"u"},
_spK,
TEXTSPLIT(
_k,
,
", "
),
_spV,
TEXTSPLIT(
_v,
,
", "
),
_flt,
FILTER(
HSTACK(
_spK,
_spV
),
ISERROR(
XMATCH(
_spK,
_vow,
0
)
),
""
),
_byR,
BYROW(
_flt,
LAMBDA(
x,
TEXTJOIN(
":",
,
x
)
)
),
TEXTJOIN(
", ",
,
_byR
)
)
)
)Excel solution 18 for Build Dictionary Excluding Vowels, proposed by Edwin Tisnado:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
f,
LAMBDA(
x,
TEXTSPLIT(
x,
,
", "
)
),
IFERROR(
ARRAYTOTEXT(
FILTER(
f(
x
)&":"&f(
y
),
ISERROR(
SEARCH(
f(
x
),
"aeiou"
)
)
)
),
""
)
)
)
)Excel solution 19 for Build Dictionary Excluding Vowels, proposed by Rayan S.:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
v,
TEXTSPLIT(
y,
", "
),
k,
TEXTSPLIT(
x,
", "
),
vw,
{"a",
"e",
"i",
"o",
"u"},
s,
vw & ":" & SEQUENCE(
100
),
t,
TEXTJOIN(
"",
,
TEXTSPLIT(
TEXTJOIN(
", ",
,
k & ":" & v
),
", " & s
)
),
IFERROR(
TEXTJOIN(
", ",
,
TEXTSPLIT(
TEXTJOIN(
"",
,
TEXTSPLIT(
t,
s
)
),
", "
)
),
""
)
)
)
)Excel solution 20 for Build Dictionary Excluding Vowels, proposed by Hazem Hassan:
=MAP(
A2:A7,
B2:B7,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
x,
,
", ",
1
),
IFERROR(
ARRAYTOTEXT(
TOCOL(
IF(
ISERROR(
VLOOKUP(
a,
{"a"; "e"; "i"; "o"; "u"},
1,
0
)
),
a & ":" & TEXTSPLIT(
y,
,
", ",
1
),
1 / 0
),
3
)
),
""
)
)
)
)Excel solution 21 for Build Dictionary Excluding Vowels, proposed by Luis Couto:
=MAP(
A2:A7;
B2:B7;
LAMBDA(
k;
v;
UNIRCADENAS(
", ";
;
MAP(
DIVIDIRTEXTO(
k;
", "
);
DIVIDIRTEXTO(
v;
", "
);
LAMBDA(
x;
y;
SI(
O(
x={"a";
"e";
"i";
"o";
"u"}
);
"";
x&":"&y
)
)
)
)
)
)Solving the challenge of Build Dictionary Excluding Vowels with Python in Excel
Python in Excel solution 1 for Build Dictionary Excluding Vowels, proposed by JvdV –:
[', '.join([x[0]+":"+x[1] for x in zip(i[0].split(', '),i[1].split(', ')) if not x[0] in 'aeiou']) for i in xl("A2:B7").values]&&