The string contains only English alphabets. Replace the alphabets with the equal or immediate past vowel. Hence, a, b, c, d will all be replaced with a. Similarly, e, f, g, h will all be replaced with e.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 100
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Replace with Past Vowels with Power Query
Power Query solution 1 for Replace with Past Vowels, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
VowelsList = {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"},
ExpectedOutput = Table.AddColumn(
Source,
"Result",
each Text.Combine(
List.Transform(
Text.ToList([String]),
(c) => List.Last(List.Select(VowelsList, (v) => v <= c))
)
)
)
in
ExpectedOutputPower Query solution 2 for Replace with Past Vowels, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "VowelReplacement"]}[Content],
Transform = Table.TransformColumns(
Source,
{},
each Text.Combine(
List.Transform(
Text.ToList(_),
(x) => List.Max(List.Select({"A", "I", "U", "E", "O", "a", "i", "u", "e", "o"}, each _ <= x))
)
)
)
in
Transform
If you wonder about the strange vowel order, that is the order of the vowels in the Japanese kana あ、い、う、え、お.
Wasn't on purpose, just the way they came to my mind. 🤦♂️
But it nicely demonstrates the advantage of using List.Max❗
Power Query solution 3 for Replace with Past Vowels, proposed by Jan Willem Van Holst:
let
reference =
let
Mytable = hashtag#table(
{"To", "From"},
{
{"a", {"a", "b", "c", "d"}},
{"e", {"e", "f", "g", "h"}},
{"i", {"i", "j", "k", "l", "m", "n"}},
{"o", {"o", "p", "q", "r", "s", "t"}},
{"u", {"u", "v", "w", "x", "y", "z" }}
}
),
small = Table.ExpandListColumn(Mytable, "From"),
intermediate = small,
caps = Table.TransformColumns(intermediate,{{"To", Text.Upper, type text}, {"From", Text.Upper, type text}})
in
Table.Combine({small,caps}),
Source = YourData,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.ToList([String])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Merged Queries" = Table.Buffer(Table.NestedJoin(#"Expanded Custom", {"Custom"}, reference, {"From"}, "Expanded Custom", JoinKind.LeftOuter)),
#"Expanded Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom", {"To"}, {"To"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Expanded Custom",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"String"}, {{"data", each Text.Combine([To])}})
in
#"Grouped Rows"
Solving the challenge of Replace with Past Vowels with Excel
Excel solution 1 for Replace with Past Vowels, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A6,
LAMBDA(
z,
CONCAT(
LOOKUP(
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
{"a",
"e",
"i",
"o",
"u"}
)
)
)
)Excel solution 2 for Replace with Past Vowels, proposed by Rick Rothstein:
=MAP(
A2:A6,
LAMBDA(
x,
LET(
b,
TOCOL(
{65,
69,
73,
79,
85}+{0;32}
),
m,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
CONCAT(
CHAR(
LOOKUP(
m,
b
)
)
)
)
)
)Excel solution 3 for Replace with Past Vowels, proposed by Rick Rothstein:
=MAP(
A2:A6,
LAMBDA(
x,
LET(
b,
{65,
69,
73,
79,
85},
s,
b+32,
m,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
CONCAT(
CHAR(
IFERROR(
LOOKUP(
m,
s
),
LOOKUP(
m,
b
)
)
)
)
)
)
)Excel solution 4 for Replace with Past Vowels, proposed by محمد حلمي:
=MAP(
A2:A6,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
v,
LOOKUP(
m,
{"a",
"e",
"i",
"o",
"u"}
),
CONCAT(
IF(
EXACT(
m,
LOWER(
m
)
),
LOWER(
v
),
UPPER(
v
)
)
)
)
)
)Excel solution 5 for Replace with Past Vowels, proposed by محمد حلمي:
=MAP(
A2:A6,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
v,
LOOKUP(
m,
{"a",
"e",
"i",
"o",
"u"}
),
CONCAT(
IF(
EXACT(
m,
LOWER(
m
)
),
LOWER(
v
),
UPPER(
v
)
)
)
)
)
)Excel solution 6 for Replace with Past Vowels, proposed by Kris Jaganah:
=BYROW(A2:A6,
LAMBDA(x,
CONCAT((CHAR(
LOOKUP(
CODE(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
)
),
CODE(
MID(
"AEIOUaeiou",
SEQUENCE(
10
),
1
)
)
)
)))))Excel solution 7 for Replace with Past Vowels, proposed by Julian Poeltl:
=MAP(
A2:A6,
LAMBDA(
S,
LET(
V,
{"a",
"e",
"i",
"o",
"u",
"A",
"E",
"I",
"O",
"U"},
CV,
CODE(
V
),
SP,
CODE(
MID(
S,
SEQUENCE(
LEN(
S
)
),
1
)
),
CONCAT(
XLOOKUP(
SP,
CV,
V,
,
-1
)
)
)
)
)Excel solution 8 for Replace with Past Vowels, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A6,
LAMBDA(
a,
LET(
uv,
{65; 69; 73; 79; 85},
lv,
{97; 101; 105; 111; 117},
seq,
SEQUENCE(
LEN(
a
)
),
splt,
MID(
a,
seq,
1
),
cd,
CODE(
splt
),
c,
IFNA(
LOOKUP(
cd,
lv
),
LOOKUP(
cd,
uv
)
),
r,
CONCAT(
CHAR(
c
)
),
r
)
)
)Excel solution 9 for Replace with Past Vowels, proposed by Timothée BLIOT:
=LET(
A,
{"a",
"e",
"i",
"o",
"u"},
B,
SORT(
TOCOL(
CODE(
HSTACK(
A,
UPPER(
A
)
)
)
)
),
T,
A2:A6,
K,
VALUE(
TEXTSPLIT(
TEXTJOIN(
"/",
1,
BYROW(
T,
LAMBDA(
x,
TEXTJOIN(
",",
1,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
)
),
",",
"/"
)
),
L,
IFERROR(
CHAR(
MAP(
K,
LAMBDA(
x,
VLOOKUP(
x,
B,
1,
-1
)
)
)
),
""
),
BYROW(
L,
LAMBDA(
x,
CONCAT(
x
)
)
)
)Excel solution 10 for Replace with Past Vowels, proposed by Bhavya Gupta:
=LET(
v,
{"a";"e";"i";"o";"u"},
c,
CODE(
VSTACK(
UPPER(
v
),
v
)
),
MAP(
A2:A6,
LAMBDA(
s,
CONCAT(
CHAR(
XLOOKUP(
CODE(
MID(
s,
SEQUENCE(
LEN(
s
)
),
1
)
),
c,
c,
,
-1
)
)
)
)
)
)Excel solution 11 for Replace with Past Vowels, proposed by Charles Roldan:
=LET(
TEXTTOARRAY,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
VowelCodes,
CODE(
TEXTTOARRAY(
"AEIOUaeiou"
)
),
MAP(
A2:A6,
LAMBDA(
x,
CONCAT(
CHAR(
LOOKUP(
CODE(
TEXTTOARRAY(
x
)
),
VowelCodes,
VowelCodes
)
)
)
)
)
)Excel solution 12 for Replace with Past Vowels, proposed by Victor Momoh (MVP, MOS, R.Eng):
=MAP(
A2:A6,
LAMBDA(
x,
LET(
a,
{"a",
"e",
"I",
"o",
"u"},
b,
UPPER(
a
),
c,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
IF(
CODE(
c
)>=97,
LOOKUP(
c,
a
),
LOOKUP(
c,
b
)
)
)
)
)
)Excel solution 13 for Replace with Past Vowels, proposed by Abhishek Kumar Jain:
=BYROW(
A2:A6,
LAMBDA(
x,
TEXTJOIN(
"",
TRUE,
LOOKUP(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
{"a",
"e",
"i",
"o",
"u"}
)
)
)
)Excel solution 14 for Replace with Past Vowels, proposed by kamal shaterian:
CONCAT(
VLOOKUP(
MID(
A2,
SEQUENCE(
LEN(
A2
)
),
1
),
$H$2:$H$13,
1,
1
)
)
from
CONCAT(
VLOOKUP(
MID(
A2,
& SEQUENCE(
LEN(
A2
)
),
1
),
H:H,
1,
1
)
).Solving the challenge of Replace with Past Vowels with Python
Python solution 1 for Replace with Past Vowels, proposed by Igor Perković:
Searching for minimum positive distance from character to vowel...
import pandas as pd
from tabulate import tabulate
# SOURCE
df = pd.read_excel('CH_100.xlsx')
# PROCESSING
vws = ['A','E','I','O','U', 'a','e','i','o','u']
vowels = [ord(c) for c in vws]
acc = []
for r in df.values.tolist():
tmp = ''
for l in r[0]:
diff_list = [ord(l)-v for v in vowels]
min_v = vws[diff_list.index(min([x for x in diff_list if x >= 0]))]
tmp = tmp + min_v
acc.append(tmp)
df['Result'] = pd.DataFrame(acc)
# RESULT
print(tabulate(df,headers=df.columns, tablefmt='psql',showindex=False),'n')
Solving the challenge of Replace with Past Vowels with SQL
SQL solution 1 for Replace with Past Vowels, proposed by Zoran Milokanović:
WITH -- Microsoft SQL Server 2019
ALPHABET
AS
(
SELECT 65 AS DEC, CHAR(65) AS CHR
UNION ALL
SELECT A.DEC + 1, CHAR(A.DEC + 1) AS CHR
FROM ALPHABET A
WHERE
A.DEC <> 122
),
DATA_PREPARATION_ALPHABET
AS
(
SELECT
A.CHR
OVER (ORDER BY A.DEC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS IMMEDIATE_PAST_VOWEL
FROM ALPHABET A
WHERE
A.DEC NOT BETWEEN 91 AND 96
),
DATA_PREPARATION_INPUT
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY F.PLACE_HOLDER) AS ORDINAL_NUMBER
,F.STRING
FROM
(
SELECT
1 AS PLACE_HOLDER
,I.STRING
FROM INPUT I
) F
)
SELECT
I.STRING
,TRANSLATE(I.STRING, A.CHR, A.IMMEDIATE_PAST_VOWEL) AS RESULT
FROM DATA_PREPARATION_INPUT I
CROSS JOIN
(
SELECT
STRING_AGG(A.CHR, '') CHR
,STRING_AGG(A.IMMEDIATE_PAST_VOWEL, '') AS IMMEDIATE_PAST_VOWEL
FROM DATA_PREPARATION_ALPHABET A
) A
ORDER BY
I.ORDINAL_NUMBER
;
&&