— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — Listed are all newcomers in the FIFA world cup. List the top 3 most frequent occurring starting alphabets of teams. Hence if team is Korea, starting alphabet is K. Note – As pointed out by Aditya Kumar Darak, first two counts are 11 and 10 which is updated in Excel file.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 76
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Frequent Starting Team Letters with Power Query
Power Query solution 1 for Frequent Starting Team Letters, proposed by Brian Julius:
https://gist.github.com/bjulius/f9b139310d606daad51da8728d66ecd9
Power Query solution 2 for Frequent Starting Team Letters, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Capitalized = Table.TransformColumns(Source, {{"The Newcomers", Text.Proper, type text}}),
Split = Table.ExpandListColumn(
Table.SelectColumns(
Table.TransformColumns(
Capitalized,
{
{
"The Newcomers",
each Splitter.SplitTextByAnyDelimiter({", ", ", And ", " And "}, QuoteStyle.Csv, true)(
_
)
}
}
),
{"The Newcomers"}
),
"The Newcomers"
),
Distinct = Table.Distinct(Split),
FirtsCharacters = Table.TransformColumns(
Distinct,
{{"The Newcomers", each Text.Start(_, 1), type text}}
),
Group = Table.Group(
FirtsCharacters,
{"The Newcomers"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
Custom = Table.ExpandListColumn(
Table.AddColumn(
Group,
"Custom",
each List.Intersect({{[Count]}, List.MaxN(List.Distinct(Group[Count]), 3)})
),
"Custom"
),
Filtered = Table.SelectRows(Custom, each [Custom] <> null and [Custom] <> ""),
SelectedCols = Table.SelectColumns(Filtered, {"The Newcomers", "Count"}),
ExpectedOutput = Table.Sort(
SelectedCols,
{{"Count", Order.Descending}, {"The Newcomers", Order.Ascending}}
)
in
ExpectedOutputPower Query solution 3 for Frequent Starting Team Letters, proposed by Jaroslaw Kujawa:
let Source = Excel.CurrentWorkbook(){[Name=Newcomers]}[Content], hashtag#Replaced Value = Table.ReplaceValue(Source, and ,, ,Replacer.ReplaceText,{The Newcomers}), hashtag#Split Column by Delimiter = Table.SplitColumn(hashtag#Replaced Value, The Newcomers, Splitter.SplitTextByDelimiter(, , QuoteStyle.Csv), {The Newcomers.1, The Newcomers.2, The Newcomers.3, The Newcomers.4, The Newcomers.5, The Newcomers.6, The Newcomers.7, The Newcomers.8, The Newcomers.9, The Newcomers.10, The Newcomers.11, The Newcomers.12, The Newcomers.13}), hashtag#Changed Type = Table.TransformColumnTypes(hashtag#Split Column by Delimiter,{{The Newcomers.1, type text}, {The Newcomers.2, type text}, {The Newcomers.3, type text}, {The Newcomers.4, type text}, {The Newcomers.5, type text}, {The Newcomers.6, type text}, {The Newcomers.7, type text}, {The Newcomers.8, type text}, {The Newcomers.9, type text}, {The Newcomers.10, type text}, {The Newcomers.11, type text}, {The Newcomers.12, type text}, {The Newcomers.13, type text}}), hashtag#Unpivoted Other Columns = Table.UnpivotOtherColumns(hashtag#Changed Type, {Year}, Attribute, Value), hashtag#Added Custom = Table.AddColumn(hashtag#Unpivoted Other Columns, Custom, each Text.Start([Value],1)), hashtag#Removed Other Columns = Table.SelectColumns(hashtag#Added Custom,{Custom}), hashtag#Grouped Rows = Table.Group(hashtag#Removed Other Columns, {Custom}, {{Count, each Table.RowCount(_), Int64.Type}}), hashtag#Sorted Rows = Table.Sort(hashtag#Grouped Rows,{{Count, Order.Descending}}), hashtag#Filtered Rows1 = Table.SelectRows(hashtag#Sorted Rows, each ([Custom] H)), hashtag#Added Custom1 = Table.AddColumn(hashtag#Filtered Rows1, Custom.1, each List.Min( List.MaxN( Table.Column( hashtag#Filtered Rows1,Count),3))=[Count]), hashtag#Filtered Rows = Table.SelectRows(hashtag#Added Custom1, each ([Custom.1] = true)), hashtag#Removed Columns = Table.RemoveColumns(hashtag#Filtered Rows,{Custom.1})in hashtag#Removed Columns
Power Query solution 4 for Frequent Starting Team Letters, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Newcomers"]}[Content],
Split = Table.ExpandListColumn(
Table.TransformColumns(
Source,
{
{
"The Newcomers",
Splitter.SplitTextByAnyDelimiter({", and ", ", ", " and "}, QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"The Newcomers"
),
Extracted = Table.TransformColumns(Split, {{"The Newcomers", each Text.Start(_, 1), type text}}),
Grouped = Table.Group(
Extracted,
{"The Newcomers"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
Grouped1 = Table.Group(
Grouped,
{"Count"},
{{"All", each _, type table [The Newcomers = text, Count = number]}}
),
Sorted = Table.Sort(Grouped1, {{"Count", Order.Descending}}),
Index = Table.AddIndexColumn(Sorted, "Index", 1, 1, Int64.Type),
Filtered = Table.SelectRows(Index, each [Index] <= 3)[[All]],
Expanded = Table.ExpandTableColumn(
Filtered,
"All",
{"The Newcomers", "Count"},
{"Letters", "Count"}
)
in
ExpandedPower Query solution 5 for Frequent Starting Team Letters, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][The Newcomers],
Letters = Table.FromColumns(
{
List.Combine(
List.Transform(
Source,
each List.Transform(
Splitter.SplitTextByAnyDelimiter({", and ", ", ", " and "})(_),
each Text.Start(_, 1)
)
)
)
},
{"Alphabet"}
),
Group = Table.Group(Letters, {"Alphabet"}, {{"Count", each Table.RowCount(_)}}),
Result = Table.MaxN(Group, "Count", each [Count] >= List.Min(List.MaxN(Group[Count], 3)))
in
ResultSolving the challenge of Frequent Starting Team Letters with Excel
Excel solution 1 for Frequent Starting Team Letters, proposed by Rick Rothstein:
=LET(c,CHAR(ROW(65:90)),s,BYROW(0+(LEFT(TEXTSPLIT(CONCAT(SUBSTITUTE(SUBSTITUTE(B1:B22,"and","")," ","")&","),","))=c),LAMBDA(x,SUM(x))),TAKE(SORT(HSTACK(c,s),2,-1),4))Excel solution 2 for Frequent Starting Team Letters, proposed by Rick Rothstein:
=LET(c,
CHAR(
ROW(
65:90
)
),
s,
BYROW(0+(LEFT(
TEXTSPLIT(
CONCAT(
SUBSTITUTE(
SUBSTITUTE(
B2:B22,
" and",
","
),
" ",
""
)&","
),
","
)
)=c),
LAMBDA(
x,
SUM(
x
)
)),
SORT(
FILTER(
HSTACK(
c,
s
),
s>=LARGE(
s,
3
)
),
2,
-1
))Excel solution 3 for Frequent Starting Team Letters, proposed by محمد حلمي:
=LET(
a,
LEFT(
TEXTSPLIT(
CONCAT(
", "&B2:B23
),
,
{", ",
", and ",
"and "}
)
),
v,
UNIQUE(
a
),
s,
MAP(v,
LAMBDA(r,
SUM(--(r=a)))),
SORT(
FILTER(
HSTACK(
v,
s
),
s>=
LARGE(
UNIQUE(
s
),
3
)
),
{2,
1},
{-1,
1}
))Excel solution 4 for Frequent Starting Team Letters, proposed by Julian Poeltl:
=LET(
L,
LEFT(
TRIM(
TEXTSPLIT(
TEXTJOIN(
", ",
,
B2:B23
),
{",",
" and "}
)
),
1
),
F,
TOCOL(
FILTER(
L,
L<>""
)
),
U,
UNIQUE(
F
),
C,
MAP(
U,
LAMBDA(
A,
COUNTA(
FILTER(
F,
F=A
)
)
)
),
SORT(
FILTER(
HSTACK(
U,
C
),
C>=LARGE(
UNIQUE(
C
),
3
)
),
2,
-1
)
)Excel solution 5 for Frequent Starting Team Letters, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_n,
3,
_d,
B2:B23,
_s,
REDUCE(
"",
_d,
LAMBDA(
a,
b,
VSTACK(
a,
LEFT(
TEXTSPLIT(
b,
{", ",
"and "},
,
TRUE
)
)
)
)
),
_c,
TOCOL(
DROP(
_s,
1
),
2
),
_a,
CHAR(
SEQUENCE(
26,
,
65
)
),
_m,
MAP(_a,
LAMBDA(a,
SUM(--(_c = a)))),
_l,
MIN(
TAKE(
SORT(
_m,
,
-1
),
_n
)
),
_r,
SORT(
FILTER(
HSTACK(
_a,
_m
),
_m >= _l
),
{2,
1},
{-1,
1}
),
_r
)Excel solution 6 for Frequent Starting Team Letters, proposed by Timothée BLIOT:
=LET(Newcomers,SUBSTITUTE(SUBSTITUTE(B2:B23," and",","),",,",","),
Split,TOCOL(IFERROR(LEFT(TEXTSPLIT(TEXTJOIN("/",1,Newcomers),", ","/",1)),"")),
Count,BYROW(FILTER(Split,Split<>""),LAMBDA(a,SUMPRODUCT(1*(FILTER(Split,Split<>"")=a)))),
Ordered,UNIQUE(SORT(HSTACK(FILTER(Split,Split<>""),Count),2,-1)),
FILTER(Ordered,INDEX(Ordered,,2)>=LARGE(UNIQUE(INDEX(Ordered,,2)),3)))Excel solution 7 for Frequent Starting Team Letters, proposed by Stefan Olsson:
=LET(
_long,
TEXTJOIN(
", ",
true,
",, ",
B2:B22
),
_noand,
REGEXREPLACE(
_long,
"bandb",
","
),
_s,
REGEXREPLACE(
_noand,
".?(, [A-Z]{1})|.",
"$1"
),
_a,
TRANSPOSE(
SPLIT(
_s,
" ,",
true,
true
)
),
_qry,
QUERY(
_a,
"Select Col1, Count(Col1) Group by Col1 Label Count(Col1) ''",
0
),
SORTN(
_qry,
3,
3,
2,
false
)
)
=SORTN(
QUERY(
{TRANSPOSE(
SPLIT(
REGEXREPLACE(
REGEXREPLACE(
TEXTJOIN(
", ",
true,
",, ",
B2:B22
),
"bandb",
","
),
".?(, [A-Z]{1})|.",
"$1"
),
" ,",
true,
true
)
)},
"Select Col1, Count(Col1) Group by Col1 Label Count(Col1) ''",
0
),
3,
3,
2,
false
)Excel solution 8 for Frequent Starting Team Letters, proposed by Gerson Pineda:
=LET(_i,
REDUCE(
"",
B2:B23,
LAMBDA(
i,
x,
VSTACK(
LEFT(
TEXTSPLIT(
SUBSTITUTE(
x,
" and ",
", "
),
,
", "
),
1
),
i
)
)
),
TAKE(SORT(HSTACK(UNIQUE(
_i
),
MAP(UNIQUE(
_i
),
LAMBDA(_x,
SUM(--(_x=_i))))),
2,
-1),
3))Excel solution 9 for Frequent Starting Team Letters, proposed by Agah Dikici:
=LET(
a,
LEFT(
TRIM(
TEXTSPLIT(
SUBSTITUTE(
TEXTJOIN(
",",
,
B2:B23
),
" and",
","
),
,
","
)
)
),
b,
FILTER(
UNIQUE(
a
),
UNIQUE(
a
)<>""
),
c,
MAP(
b,
LAMBDA(
x,
ROWS(
FILTER(
a,
a=x
)
)
)
),
SORT(
FILTER(
HSTACK(
b,
c
),
c>=LARGE(
UNIQUE(
c
),
3
)
),
2,
-1
)
)