You will need to see which Greek alphabet appears maximum no. of times. You will need to repeat all other Greek alphabets same number of times. Seq No. will be missing for every new row inserted. Beta appears 4 times which is maximum. Hence, we need to repeat Alpha, Gamma and Delta also 4 times.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 28
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Repeat Greek alphabets to match with Power Query
Power Query solution 1 for Repeat Greek alphabets to match, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Greek"},
{{"All", each _[Seq]}, {"Cnt", each Table.RowCount(_), Int64.Type}}
),
AddedCustom = Table.AddColumn(
Grouped,
"Seq",
each [All] & List.Repeat({null}, List.Max(Grouped[Cnt]) - [Cnt])
)[[Seq], [Greek]],
Expanded = Table.ExpandListColumn(AddedCustom, "Seq")
in
ExpandedPower Query solution 2 for Repeat Greek alphabets to match, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Seq", Int64.Type}, {"Greek", type text}}),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Greek"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
CalcMax = List.Max(#"Grouped Rows"[Count]),
Back = #"Grouped Rows",
#"Added Custom" = Table.AddColumn(Back, "Repeat", each {1 .. CalcMax}),
#"Expanded Repeat" = Table.ExpandListColumn(#"Added Custom", "Repeat"),
#"Added Conditional Column" = Table.AddColumn(
#"Expanded Repeat",
"Custom",
each if [Repeat] <= [Count] then 1 else null
),
#"Added Index" = Table.AddIndexColumn(#"Added Conditional Column", "Index", 1, 1, Int64.Type),
#"Added Conditional Column1" = Table.AddColumn(
#"Added Index",
"Custom.1",
each if [Custom] = null then null else List.Sum(List.FirstN(#"Added Index"[Custom], [Index]))
),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1", {{"Custom.1", "Seq"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns", {"Seq", "Greek"})
in
#"Removed Other Columns"Power Query solution 3 for Repeat Greek alphabets to match, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "GreekLetters"]}[Content],
Index1 = Table.AddIndexColumn(Source, "Index1", 0, 1, Int64.Type),
Grouped = Table.Group(
Index1,
{"Greek"},
{{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _}}
),
Index = Table.AddIndexColumn(Grouped, "Index", 0, 1, Int64.Type),
Subtracted = Table.TransformColumns(
Index,
{{"Count", each List.Max(Index[Count]) - _, type number}}
),
List = Table.AddColumn(Subtracted, "Custom", each List.Repeat({[Greek]}, [Count])),
Expanded = Table.ExpandListColumn(List, "Custom")[[Custom], [Index]],
Filtered = Table.SelectRows(Expanded, each ([Custom] <> null)),
Renamed = Table.RenameColumns(Filtered, {{"Custom", "Greek"}}),
Index2 = Table.AddIndexColumn(Renamed, "Index1", 100, 1, Int64.Type),
ExpandedAll = Table.ExpandTableColumn(List, "All", {"Seq", "Index1"})[
[Seq],
[Greek],
[Index],
[Index1]
],
Appended = Table.Combine({ExpandedAll, Index2}),
Sorted = Table.Sort(Appended, {{"Index", Order.Ascending}, {"Index1", Order.Ascending}})[
[Seq],
[Greek]
]
in
SortedPower Query solution 4 for Repeat Greek alphabets to match, proposed by Antriksh Sharma:
let
Source = Datasource,
MaxGreek = Table.Max(
Table.Group(Source, {"Greek"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
"Count"
),
Transform = Table.Group(
Source,
"Greek",
{
{
"Group",
(CurrentGroup) =>
let
RowCount = Table.RowCount(CurrentGroup),
MaxRows = MaxGreek[Count],
TableToRepeat = Table.FromRows(
{{null, CurrentGroup[Greek]{0}}},
type table [Seq = Int64.Type, Greek = text]
),
Check =
if RowCount < MaxRows then
CurrentGroup & Table.Repeat(TableToRepeat, MaxRows - RowCount)
else
CurrentGroup
in
Check,
type table [Seq = Int64.Type, Greek = text]
}
}
),
RemovedColumns = Table.RemoveColumns(Transform, {"Greek"}),
ExpandedGroup = Table.ExpandTableColumn(
RemovedColumns,
"Group",
{"Seq", "Greek"},
{"Seq", "Greek"}
)
in
ExpandedGroupPower Query solution 5 for Repeat Greek alphabets to match, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Greek"},
{{"Seq", each _[Seq]}, {"count", each List.Count([Greek])}}
),
Add = Table.AddColumn(
Grouped,
"Custom",
each Table.FromColumns(
{[Seq], List.Repeat({[Greek]}, List.Max(Grouped[count]))},
{"Seq", "Greek"}
)
),
Combine = Table.Combine(Add[Custom])
in
CombinePower Query solution 6 for Repeat Greek alphabets to match, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
num = List.Count(List.Select(Source[Greek], each _ = List.Mode(Source[Greek]))),
#"Grouped Rows" = Table.Group(
Source,
{"Greek"},
{{"Count", each Table.FromColumns({_[Seq], List.Repeat({_[Greek]{0}}, num)}, {"Seq", "Greek"})}}
)[Count],
Custom1 = Table.Combine(#"Grouped Rows")
in
Custom1Power Query solution 7 for Repeat Greek alphabets to match, proposed by Abdoul Karim N.:
let
Source = Excel.CurrentWorkbook(){[Name = "Geek"]}[Content],
ChangedType = Table.TransformColumnTypes(Source, {{"Seq", Int64.Type}, {"Greek", type text}}),
Index = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
Groupeded = Table.Group(Index, {"Greek"}, {{"Occurence", each Table.RowCount(_), Int64.Type}}),
GetList = Table.AddColumn(Groupeded, "ListRepetition", each List.Repeat({[Greek]}, [Occurence])),
Expantion = Table.ExpandListColumn(GetList, "ListRepetition"),
GetSequence = Table.AddColumn(Expantion, "Seq", each " ")[[Seq], [ListRepetition]],
Renaming = Table.RenameColumns(GetSequence, {{"ListRepetition", "Greek"}}),
Append = Table.Combine({Index, Renaming}),
Sorting = Table.Sort(Append, {{"Greek", Order.Ascending}, {"Seq", Order.Ascending}})[
[Seq],
[Greek]
]
in
SortingSolving the challenge of Repeat Greek alphabets to match with Excel
Excel solution 1 for Repeat Greek alphabets to match, proposed by Rick Rothstein:
=LET(
a,
B2:B11,
u,
UNIQUE(
a
),
c,
COUNTIF(
a,
u
),
m,
MAX(
c
),
g,
TEXTSPLIT(
CONCAT(
MAP(
u,
LAMBDA(
x,
REPT(
x&"|",
m
)
)
)
),
,
"|"
),
s,
TEXTSPLIT(
CONCAT(
MAP(
u,
c,
LAMBDA(
y,
z,
CONCAT(
FILTER(
A2:A11,
a=y
)&"|"
)&REPT(
"|",
m-z
)
)
)
),
,
"|"
),
HSTACK(
s,
g
)
)Excel solution 2 for Repeat Greek alphabets to match, proposed by محمد حلمي:
=LET(
b,
B2:B11,
REDUCE(
A1:B1,
UNIQUE(
b
),
LAMBDA(
A,
D,
LET(
v,
FILTER(
A2:B11,
b=D
),
r,
INDEX(
v,
1,
2
),
VSTACK(
A,
IFNA(
HSTACK(
TAKE(
v,
,
1
),
EXPAND(
r,
MAX(
COUNTIF(
B2:B11,
b
)
),
,
r
)
),
""
)
)
)
)
)
)Excel solution 3 for Repeat Greek alphabets to match, proposed by محمد حلمي:
=LET(
b,
B2:B11,
a,
UNIQUE(
b
),
v,
COUNTIF(
b,
b
),
r,
MAX(
v
),
x,
REPT(
a&" ",
r
),
c,
TEXTSPLIT(
CONCAT(
x
),
,
" ",
1
),
n,
XMATCH(
c,
c
),
m,
SEQUENCE(
r*4
),
k,
IF(
n=m,
COUNTIF(
b,
c
)
),
IFERROR(
HSTACK(
TRIM(
TEXTSPLIT(
CONCAT(
IFERROR(
MAP(
k,
IF(
k,
XLOOKUP(
c,
b,
A2:A11
)
),
LAMBDA(
a,
d,
CONCAT(
SEQUENCE(
a,
,
d
)&"- "
)
)
),
""
)&"-"
),
,
"-",
1
)
)+0,
c
),
""
)
)Excel solution 4 for Repeat Greek alphabets to match, proposed by 🇰🇷 Taeyong Shin:
=LET(
s,
A2:A11,
g,
B2:B11,
c,
SEQUENCE(
,
MAX(
COUNTIF(
g,
g
)
)
),
u,
UNIQUE(
g
),
n,
MINIFS(
s,
g,
u
)+c-1,
HSTACK(
TOCOL(
IF(
n<=MAXIFS(
s,
g,
u
),
n,
""
)
),
TOCOL(
u&T(
c
)
)
)
)Excel solution 5 for Repeat Greek alphabets to match, proposed by 🇰🇷 Taeyong Shin:
=LET(
Seq,
A2:A11,
Greek,
B2:B11,
m,
MAX(
COUNTIF(
Greek,
Greek
)
),
Ugreek,
UNIQUE(
Greek
),
G,
TEXTSPLIT(
ARRAYTOTEXT(
TRIM(
REPT(
Ugreek & " ",
m
)
)
),
,
{" ";", "}
),
S,
DROP(
REDUCE(
"",
Ugreek,
LAMBDA(
a,
b,
VSTACK(
a,
EXPAND(
FILTER(
Seq,
Greek = b
),
m,
,
""
)
)
)
),
1
),
HSTACK(
S,
G
)
)Excel solution 6 for Repeat Greek alphabets to match, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_g,
B2:B11,
_u,
UNIQUE(
_g
),
_m,
MAX(
COUNTIFS(
_g,
_u
)
),
_calc,
REDUCE(
"",
_u,
LAMBDA(
a,
b,
VSTACK(
a,
HSTACK(
EXPAND(
FILTER(
A2:A11,
_g = b
),
_m,
,
""
),
EXPAND(
b,
_m,
,
b
)
)
)
)
),
DROP(
_calc,
1
)
)Excel solution 7 for Repeat Greek alphabets to match, proposed by Duy Tùng:
=LET(
a,
B2:B11,
REDUCE(
A1:B1,
UNIQUE(
a
),
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
HSTACK(
FILTER(
A2:A11,
a=y
),
TEXTSPLIT(
CONCAT(
REPT(
y&"/",
MAX(
COUNTIF(
a,
a
)
)
)
),
,
"/",
1
)
)
),
""
)
)
)
)Excel solution 8 for Repeat Greek alphabets to match, proposed by Bhavya Gupta:
=LET(Greek,
B2:B11,
u,
UNIQUE(
Greek
),
m,
MAX(MAP(u,
LAMBDA(x,
SUM(--(x=Greek))))),
DROP(
REDUCE(
0,
u,
LAMBDA(
a,
b,
VSTACK(
a,
IFNA(
HSTACK(
& FILTER(
A2:A11,
Greek=b
),
EXPAND(
b,
m,
,
b
)
),
""
)
)
)
),
1
))Excel solution 9 for Repeat Greek alphabets to match, proposed by Viswanathan M B:
=LET(
Seq,
A2:A11,
Greeks,
B2:B11,
Unq,
UNIQUE(
Greeks
),
Size,
MAX(
COUNTIFS(
Greeks,
Unq
)
),
N,
ROWS(
Unq
)*Size,
List,
INDEX(
Unq,
ROUNDUP(
SEQUENCE(
N
)/Size,
0
)
),
Sn,
DROP(
REDUCE(
"",
SEQUENCE(
Size
),
LAMBDA(
a,
b,
VSTACK(
a,
EXPAND(
FILTER(
Seq,
Greeks=INDEX(
Unq,
b
)
),
Size,
,
""
)
)
)
),
1
),
HSTACK(
Sn,
List
)
)&&