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

Post answers in Comment.

(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)

Download Practice File - https://lnkd.in/dpxZzUAg

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

Excel BI’s LinkedIn Post

Linkedin Post by:
Alejandro Simón

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

LinkedIn Post by:
Aditya Kumar Darak

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

LinkedIn Post by:
Gabriel Raigosa

:arrow_forward: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))

:arrow_forward: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

Adjunto mi query…

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

LinkedIn Post by:
Brian Julius

My #powerquery solution:

let
CurrentYear = Date.Year( DateTime.FixedLocalNow() ),
Source = Table.FromList({1900..CurrentYear}, Splitter.SplitByNothing(), {"Answer"}, null, ExtraValues.Error),
AddSumDigits = Table.AddColumn(Source, "SumDigits", each
[
a = [Answer],
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

LinkedIn Post by:
Sunny Baggu

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)))))

LinkedIn Post by:
Muhammad Rayan S.

=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))

LinkedIn Post by:
Luan Rodrigues

let
  Fonte = {1900..9999},
  tab = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null),
  add = Table.AddColumn(tab, "Personalizar", each 
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))

LinkedIn post by:
Bo Rydobon

=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))

LinkedIn Post by:
Zoran Milokanović

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

LinkedIn Post by:
Lorenzo Foti

=+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)

LinkedIn post by:
محمد حلمي

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

LinkedIn post by:
Julien Lacaze

=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)

LinkedIn post by:
Stevenson Yu

=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))

LinkedIn Post by:
Aditya Kumar Darak

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

LinkedIn post by:
Rick Rothstein

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