List the Disarium numbers from column A. A Disarium number is that number where sum of each digit raised to the power of its position in the number is equal to number itself. Position follows index 1 method not index 0. Hence in 265, position of 2 is 1 and position of 6 is 2 and position of 5 is 3. Ex. 175 is a Disarium number. 1^1+7^2+5^3 = 1+49+125=175
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 124
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Disarium Numbers with Power Query
Power Query solution 1 for Find Disarium Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fil = Table.SelectRows(
Source,
each
let
D = Text.ToList(Text.From([Number]))
in
List.Sum(
List.Transform({1 .. List.Count(D)}, (n) => Number.Power(Number.From(D{n - 1}), n))
)
= [Number]
)
in
FilPower Query solution 2 for Find Disarium Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calculation = Table.AddColumn(
Source,
"Calc",
each [
Split = Text.ToList(Text.From([Number])),
Count = List.Count(Split),
Total = List.Sum(
List.Transform({1 .. Count}, (f) => Number.Power(Number.From(Split{f - 1}), f))
),
Check = Total = [Number]
][Check]
),
Return = Table.SelectRows(Calculation, each ([Calc] = true))[[Number]]
in
ReturnPower Query solution 3 for Find Disarium Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Duplicated = Table.DuplicateColumn(Source, "Number", "Copy"),
Copy = Table.TransformColumns(
Duplicated,
{"Copy", each List.Transform(Text.ToList(Text.From(_)), Number.From)}
),
Added = Table.AddColumn(Copy, "Custom", each {1 .. List.Count([Copy])}),
Zipped = Table.AddColumn(Added, "Zipped", each List.Zip({_[Copy], _[Custom]}))[[Number], [Zipped]],
ZippedExp = Table.ExpandListColumn(Zipped, "Zipped"),
Potencia = Table.AddColumn(
ZippedExp,
"Disarium Number",
each Number.Power([Zipped]{0}, [Zipped]{1})
),
Sol = Table.SelectRows(
Table.Group(Potencia, {"Number"}, {{"Disarium Number", each List.Sum([Disarium Number])}}),
each [Number] = [Disarium Number]
)[[Disarium Number]]
in
SolPower Query solution 4 for Find Disarium Numbers, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DuplicateCol = Table.DuplicateColumn(Source, "Number", "Num"),
Split = Table.ExpandListColumn(
Table.TransformColumns(
Table.TransformColumnTypes(DuplicateCol, {{"Num", type text}}, "en-US"),
{
{
"Num",
Splitter.SplitTextByRepeatedLengths(1),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Num"
),
ReType = Table.TransformColumnTypes(Split, {{"Num", Int64.Type}}),
Group = Table.Group(ReType, {"Number"}, {"All", each _, type table}),
AddPosCol = Table.ExpandTableColumn(
Table.RemoveColumns(
Table.AddColumn(Group, "AddPos", each Table.AddIndexColumn([All], "Pos", 1, 1)),
"All"
),
"AddPos",
{"Num", "Pos"}
),
AddPower = Table.AddColumn(AddPosCol, "Power", each Number.Power([Num], [Pos])),
SumPower = Table.Group(
AddPower,
{"Number"},
{{"Disarium Number", each List.Sum([Power]), type number}}
),
Select = Table.RemoveColumns(
Table.SelectRows(SumPower, each [Number] = [#"Disarium Number"]),
"Number"
)
in
SelectPower Query solution 5 for Find Disarium Numbers, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangeTypeText = Table.TransformColumnTypes(Source, {{"Number", Text.Type}}),
DisariumCalculation = Table.AddColumn(
ChangeTypeText,
"Personalizado",
each List.Sum(
Table.AddColumn(
Table.AddIndexColumn(
Table.TransformColumnTypes(
Table.FromList(
Text.ToList([Number]),
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
{"Column1", type number}
),
"Power",
1
),
"Number2",
each Number.Power([Column1], [Power])
)[Number2]
),
type number
),
ChangeTypeToNumber = Table.TransformColumnTypes(DisariumCalculation, {{"Number", type number}}),
Result = Table.SelectRows(
Table.AddColumn(ChangeTypeToNumber, "Compare", each [Number] = [Personalizado]),
each [Compare] = true
)[[Number]]
in
ResultSolving the challenge of Find Disarium Numbers with Excel
Excel solution 1 for Find Disarium Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:A10,
FILTER(
z,
z=MAP(
z,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
SUM(
MID(
a,
s,
1
)^s
)
)
)
)
)
)Excel solution 2 for Find Disarium Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(z,
A2:A10,
s,
SEQUENCE(
,
9
),
FILTER(z,
z=MMULT(--(0&MID(
z,
s,
1
))^s,
TOCOL(
s
)^0)))Excel solution 3 for Find Disarium Numbers, proposed by Rick Rothstein:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
SEQUENCE(
LEN(
x
)
),
SUM(
MID(
x,
s,
1
)^s
)=x
)
)
)
)Excel solution 4 for Find Disarium Numbers, proposed by John V.:
=LET(
n,
A2:A10,
c,
COLUMN(
A:I
),
FILTER(
n,
n=MMULT(
MID(
n*10^8,
c,
1
)^c,
n^0
)
)
)Excel solution 5 for Find Disarium Numbers, proposed by Julian Poeltl:
=LET(
N,
A2:A10,
FILTER(
N,
N=MAP(
N,
LAMBDA(
N,
LET(
L,
SEQUENCE(
LEN(
N
)
),
SUM(
MID(
N,
L,
1
)^L
)
)
)
)
)
)Excel solution 6 for Find Disarium Numbers, proposed by Alejandro Campos:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
digits,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
total,
SUMPRODUCT(
VALUE(
digits
) ^ SEQUENCE(
LEN(
x
)
)
),
total = x
)
)
)
)Excel solution 7 for Find Disarium Numbers, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:A10,
_e1,
LAMBDA(
x,
SEQUENCE(
LEN(
x
)
)
),
_e2,
LAMBDA(a,
SUM(MID(a,
_e1(a),
1) ^ _e1(a))),
_c,
MAP(
A2:A10,
_e2
),
_r,
FILTER(
_d,
_d = _c
),
_r
)Excel solution 8 for Find Disarium Numbers, proposed by Timothée BLIOT:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(a,
SUM(MAP(SEQUENCE(
LEN(
a
)
),
LAMBDA(x,
MID(
a,
x,
1
)^(x) )) ) ))=A2:A10)Excel solution 9 for Find Disarium Numbers, proposed by Hussein SATOUR:
=LET(
n,
A2:A10,
d,
MAP(
n,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
SEQUENCE(
a
),
c,
--MID(
x,
b,
1
),
SUM(
POWER(
c,
b
)
)
)
)
),
FILTER(
d,
n = d
)
)Excel solution 10 for Find Disarium Numbers, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
_A,
SEQUENCE(
LEN(
a
)
),
_B,
MID(
a,
_A,
1
),
SUM(
POWER(
_B,
_A
)
)=a
)
)
)
)
=FILTER(
A2:A10,
DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
a,
v,
LET(
_A,
SEQUENCE(
LEN(
v
)
),
_B,
MID(
v,
_A,
1
),
VSTACK(
a,
SUM(
POWER(
_B,
_A
)
)=v
)
)
)
),
1
)
)Excel solution 11 for Find Disarium Numbers, proposed by Md. Zohurul Islam:
=LET(
z,
A2:A10,
p,
MAP(
z,
LAMBDA(
x,
LET(
a,
LEN(
x
),
b,
SEQUENCE(
,
a
),
c,
--MID(
x,
b,
1
),
d,
SUM(
c^b
),
e,
IF(
d=x,
1,
0
),
e
)
)
),
q,
VSTACK(
"Disarium Number",
FILTER(
z,
p>0
)
),
q
)Excel solution 12 for Find Disarium Numbers, proposed by Jaroslaw Kujawa:
=LET(c , BYROW(A2:A11 , LAMBDA(a , LET(b , SEQUENCE( , LEN(a) , 1) , IF(SUM(MID(a , b , 1)^b)=a , a , 0)))) , FILTER(c , c>0))Excel solution 13 for Find Disarium Numbers, proposed by Abhishek Kumar Jain:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
SEQUENCE(
LEN(
x
)
),
b,
MID(
x,
a,
1
),
x=SUM(
b^a
)
)
)
)
)Excel solution 14 for Find Disarium Numbers, proposed by Guillermo Arroyo:
=LET(_q,
A2:A10,
FILTER(_q,
MAP(_q,
LAMBDA(_n,
LET(_l,
SEQUENCE(
LEN(
_n
)
),
SUM(--(MID(
_n,
_l,
1
))^_l))))=_q,
""))
=LET(
_m,
A2:A10,
_f,
LAMBDA(
_a,
_b,
_c,
_d,
IF(
_b="",
_c,
_a(
_a,
MID(
_b,
2,
99
),
_c+LEFT(
_b,
1
)^_d,
_d+1
)
)
),
FILTER(
_m,
_m=MAP(
_m,
LAMBDA(
_q,
_f(
_f,
_q,
0,
1
)
)
),
""
)
)Excel solution 15 for Find Disarium Numbers, proposed by Anup Kumar:
=LET(
stck,
VSTACK(
B3:B6,
D5:D8,
F4:F9
),
COUNT(
FILTER(
stck,
stck>50
)
)
)Excel solution 16 for Find Disarium Numbers, proposed by Fábio Gatti:
=LAMBDA(
Rng,
LET(
fxDisarium,
LAMBDA(
Number,
& LET(
i,
LEN(
Number
),
p,
SEQUENCE(
i
),
n,
--MID(
Number,
p,
1
),
v,
n^p,
SUM(
v
)
)
),
FILTER(
Rng,
BYROW(
Rng,
fxDisarium
)=Rng
)
)
)(A2:A10)Excel solution 17 for Find Disarium Numbers, proposed by roberto mensa:
=FILTER(
A2:A10,
A2:A10=MMULT(
MID(
A2:A10&REPT(
0,
15-LEN(
A2:A10
)
),
SEQUENCE(
,
15
),
1
)^SEQUENCE(
,
15
),
SEQUENCE(
15,
,
1,
0
)
)
)Excel solution 18 for Find Disarium Numbers, proposed by Tushar Mehta:
=LAMBDA(
_rng,
FILTER(
_rng,
MAP(
_rng,
LAMBDA(
_n,
LET(
_ref,
"identify a disarium number",
_seq,
SEQUENCE(
LEN(
_n
)
),
_digits,
MID(
_n,
_seq,
1
),
SUM(
_digits^_seq
)=_n
)
)
)
)
)(A2:A10)Solving the challenge of Find Disarium Numbers with Python in Excel
Python in Excel solution 1 for Find Disarium Numbers, proposed by Alejandro Campos:
def is_disarium_number(num):
digits = list(str(num))
total = sum(int(digit) ** (i + 1) for i, digit in enumerate(digits))
return total == num
numbers = xl("A2:A10")[0]
disarium_numbers = [num for num in numbers if is_disarium_number(num)]
df = pd.DataFrame(disarium_numbers, columns=['Number'])
df
Solving the challenge of Find Disarium Numbers with SQL
SQL solution 1 for Find Disarium Numbers, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
CALC
AS
(
SELECT
D.NUMBER
,SUBSTRING(D.NUMBER, 1, 1) AS NUM
,SUBSTRING(D.NUMBER, 2, LEN(D.NUMBER) - 1) AS REMAINDER
,1 AS EXPONENT
,POWER(SUBSTRING(D.NUMBER, 1, 1), 1) AS DISARIUM_NUMBER
FROM DATA D
UNION ALL
SELECT
C.NUMBER
,SUBSTRING(C.REMAINDER, 1, 1) AS NUM
,SUBSTRING(C.REMAINDER, 2, LEN(C.REMAINDER) - 1) AS REMAINDER
,C.EXPONENT + 1 AS EXPONENT
,C.DISARIUM_NUMBER + POWER(SUBSTRING(C.REMAINDER, 1, 1), C.EXPONENT + 1) AS DISARIUM_NUMBER
FROM CALC C
WHERE
C.REMAINDER <> ''
)
SELECT
C.DISARIUM_NUMBER
FROM CALC C
WHERE
C.REMAINDER = ''
AND C.DISARIUM_NUMBER = C.NUMBER
ORDER BY
CAST(C.NUMBER AS INTEGER)
;
&&