# Excel BI Challenge Workout 192

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 192 —
(Solutions in any language are also welcome for Excel Challenges)

Excel formulas permit date range from 1-Jan-1900 to 31-Dec-9999.
List the years where
Year + Sum of Digits of Year = Current Year
Ex. 1997
1997 + 1 + 9 + 9 + 7 = 2023

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

Adjunto otra query solo utilizando funciones de listas…

``````let
Source = Date.Year(DateTime.LocalNow()),
Sol = List.Transform([List.Select](http://list.select/)(List.Transform({1900..9999}, each
let
a = _,
b = Text.ToList(Text.From(a)),
c = List.Sum(List.Transform(b, each Number.From(_)))+a,
d = [List.Zip](http://list.zip/)({{a},{c}}){0}
in d), each _{1} = Source), each _{0})
in
Sol
``````

Here is one way to do it in Excel

``````=LET(
_tday, TODAY(),
_y, YEAR(_tday),
_sq, SEQUENCE(_y - 1900, , 1900),
_c, MAP(_sq, LAMBDA(a, SUM(a, --MID(a, {1, 2, 3, 4}, 1)))),
_r, FILTER(_sq, _c = _y),
_r
)

Here:
tday = Today
y = Year
sq = Sequence
c = Calculation
r = Return
``````

EN:

`=LET(s,SEQUENCE(8100,,1900),FILTER(s,BYROW(MID(s,SEQUENCE(,4),1)*1,LAMBDA(x,SUM(x)))+s=YEAR(TODAY())))`

`=LET(s,SEQUENCE(8100,,1900),FILTER(s,BYROW(MID(s,SEQUENCE(,4),1)*1,LAMBDA(x,SUM(x)))+s=2023))`

`=LET(s,SEQUENCE(8100,,1900),FILTER(s,BYROW(MID(s,{1,2,3,4},1),LAMBDA(x,SUM(x*1)))+s=2023))`

ES:

`=LET(s,SECUENCIA(8100,,1900),FILTRAR(s,BYROW(EXTRAE(s,SECUENCIA(,4),1)*1,LAMBDA(x,SUMA(x)))+s=AÑO(HOY())))`

`=LET(s,SECUENCIA(8100,,1900),FILTRAR(s,BYROW(EXTRAE(s,SECUENCIA(,4),1)*1,LAMBDA(x,SUMA(x)))+s=2023))`

`=LET(s,SECUENCIA(8100,,1900),FILTRAR(s,BYROW(EXTRAE(s,{1,2,3,4},1),LAMBDA(x,SUMA(x*1)))+s=2023))`

## LinkedIn Post by: Alejandro Simón

``````let
Source = Date.Year(DateTime.LocalNow()),
Lst2Tbl = Table.FromColumns({{1900..9999}}),
Process = Table.AddColumn(Lst2Tbl, "Custom", each let
a = [Column1],
b = Text.ToList(Text.From(a)),
c = List.Sum(List.Transform(b, each Number.From(_)))+a
in c),
Sol = Table.SelectRows(Process, each [Custom] = Source )[Column1]
in
Sol
``````

My #powerquery solution:

``````let
CurrentYear = Date.Year( DateTime.FixedLocalNow() ),
Source = Table.FromList({1900..CurrentYear}, Splitter.SplitByNothing(), {"Answer"}, null, ExtraValues.Error),
[
b = Text.From(a),
c = Text.ToList(b),
d = List.Transform(c, each Number.From(_)),
e = List.Sum( d ),
f = a + e
][f]),
Filter = Table.RemoveColumns( Table.SelectRows(AddSumDigits, each [SumDigits] = CurrentYear), "SumDigits")
in
Filter
``````

1#

``````=LET(_year,SEQUENCE(YEAR(NOW())-1900,,1900),
FILTER(_year,MAP(_year,LAMBDA(a,YEAR(NOW())=SUM(a,MID(a,SEQUENCE(LEN(a)),1)+0)))))
``````

2#

``````=LET(_year,SEQUENCE(YEAR(NOW())-1900,,1900),
FILTER(_year,MAP(_year,LAMBDA(a,YEAR(NOW())=a+SUM(RIGHT(INT(a/10^SEQUENCE(,4,0)))+0)))))
``````

``````=LET(y, SEQUENCE(9999 - 1899, , 1900, 1), Break, HSTACK(LEFT(y, 1) + 0, MID(y, 2, 1) + 0, MID(y, 3, 1) + 0, RIGHT(y, 1) + 0), sum, BYROW(Break, LAMBDA(x, SUM(x))), f, y + sum, FILTER(y, f = 2023))
``````

``````let
Fonte = {1900..9999},
tab = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null),
List.Sum(List.Transform(Text.ToList(Text.From([Column1])),Number.From))+[Column1] ),
res = Table.SelectRows(add, each ([Personalizar] = Date.Year(DateTimeZone.LocalNow())))[[Column1]]
in
res
``````

## LinkedIn Post by:Pieter de B.

``````=LET(start,DATE(1900,1,1),end,DATE(9999,12,31),y,YEAR(EDATE(start,SEQUENCE(1+DATEDIF(start,end,"y"),,0,12))),s,--(MID(y,SEQUENCE(1,4),1)),FILTER(y,BYROW(HSTACK(y,s),LAMBDA(x,SUM(x)=YEAR(TODAY())))))
``````

## LinkedIn Post by:Hussain Ali Nasser

``````=LET(_seq,SEQUENCE(YEAR(TODAY())-1900+1,,1900),FILTER(_seq,BYROW(_seq,LAMBDA(_range,LET(_splitrange,--MID(_range,SEQUENCE(,4),1),_sumsplit,SUM(_range,_splitrange),_sumsplit=YEAR(TODAY()))))))
``````

## LinkedIn Post by:Amr Tawfik, CMA P1

With more Cosmetics

`=LET(tt,SEQUENCE((9999-1900)+1,,1900,1),FILTER(tt,BYROW(--MID(tt,SEQUENCE(,4,1,1),1),LAMBDA(x,SUM(x)))+tt=2023))`

`=LET(n,YEAR(NOW()),s,SEQUENCE(YEAR(NOW())-1900,,1900),TOCOL(s/(s-MMULT(-MID(s,{1,2,3,4},1),{1;1;1;1})=n),3))`

Listing years w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi

``````let
Solution = [List.Select](http://list.select/)(List.Transform({1900..9999}, each Text.From(_)), (n) =>
Expression.Evaluate(n & Text.Combine(List.Transform(Text.ToList(n), each "+" & _))) = Date.Year(DateTime.LocalNow())
)
in
Solution
``````

``````=+LET(
rng;SEQUENCE(9999-1900;;1900);
checkSum;BYROW(rng;LAMBDA(row;IF(row+SUM(NUMBERVALUE(MID(row;SEQUENCE(4);1)))=2023;1;0)));
outP;FILTER(rng;checkSum=1);
outP)
``````

``````=LET(r,SEQUENCE(8100,,1900),FILTER(r,BYROW(r,LAMBDA(a,a+SUM(--MID(a,SEQUENCE(,4),1))))=YEAR(NOW())))
``````

``````=LET(years,SEQUENCE(1+9999-1900,,1900),
sums,BYROW(years,LAMBDA(a,SUM(a,NUMBERVALUE(MID(a,SEQUENCE(4),1))))),
result,FILTER(years,sums=YEAR(TODAY())),
result)
``````

``````=LET(
A,SEQUENCE(8100,,1900),
B, --MID(A,{1,2,3,4},1),
C, A+BYROW(B,LAMBDA(B,SUM(B))),
D, C=YEAR(NOW()),
FILTER(A,D))
``````

Here is one way out in Power Query.

``````let
Today = DateTimeZone.UtcNow(),
Year  = Date.Year ( Today ),
List  = { 1900 .. Year - 1 },
Return = List.Select (
List,
each _ + List.Sum ( List.Transform ( Text.ToList ( Text.From ( _ ) ), Number.From ) ) = Year
)
in
Return
``````

`=LET(s,SEQUENCE(400,,1900),FILTER(s,MAP(s,LAMBDA(x,2023=x+SUM(0+MID(x,{1,2,3,4},1))))))`