Excel BI Challenge Workout 199

Workouts Excel BI Challenge
1

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

List the count of dates along with Min and Max date for the rows in columns A & B where MM + DD = YY.
(Dates given are in MDY format)
For example : 12/13/2025 where 12+13 = 25 which is equal to YY part of the year which is 25.

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/dnQ-N_7j

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

image
image800×450 107 KB

LinkedIn Post:

2

LinkedIn Post by:
Alejandro Simón

Adjunto mi query…

Adjunto mi query... 
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Custom", each
let
a = {Number.From([From Date])..Number.From([To Date])},
b = List.Transform(a, each Date.From(_)),
c = [List.Select](http://list.select/)(b, each [Date.Day](http://date.day/)(_) + Date.Month(_) = Number.From(Date.ToText(_, "yy"))),
d = [Count = List.Count(c), Min Date = List.Min(c), Max Date = List.Max(c)]
in d)[Custom],
Sol = Table.FromRecords(AddCol)
in
Sol
3

LinkedIn post by:
Rick Rothstein

=0+TEXTSPLIT(TEXTJOIN("/",,MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(s,SEQUENCE(b-a,,a),f,FILTER(s,MONTH(s)+DAY(s)=TEXT(s,"yy")+0),COUNT(f)&" "&MIN(f)&" "&MAX(f)))))," ","/")

4

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

=TEXTSPLIT(CONCAT(MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(d,SEQUENCE(b-a+1,,a),
r,FILTER(d,DAY(d)+MONTH(d)=YEAR(d)-2000),CONCAT(HSTACK(ROWS(r),MIN(r),MAX(r))&" "))))&"-")," ","-",1)+0
5

LinkedIn Post by:
Guillermo Arroyo

TEXTSPLIT(TEXTJOIN("#",,LET(d,SEQUENCE(31),m,SEQUENCE(,12),f,SORT(TOCOL(DATEVALUE(m&"-"&d&"-"&2000+d+m),3)),MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(n,FILTER(f,(f>=a)*(f<=b)),TEXTJOIN("|",,COUNT(n),MIN(n),MAX(n))))))),"|","#")

6

LinkedIn Post by:
Hussain Ali Nasser
 

TEXTSPLIT(
TEXTJOIN("/",,
MAP(A2:A6,B2:B6,
LAMBDA(_from,_to,
LET(
_dates,SEQUENCE(1+_to-_from,,_from,1),
_month,MONTH(_dates),
_day,DAY(_dates),
_year,--RIGHT(YEAR(_dates),2),
_check,_day+_month=_year,
_filter,FILTER(_dates,_check),
_count,COUNT(_filter),
_min,MIN(_filter),
_max,MAX(_filter),
_count&" "&_min&" "&_max
)
)
))," ","/")
7

LinkedIn Post by:
Pieter de B.

not sure to use min/max versus take. I expect take to calculate faster, but haven’t tested (using phone)

TEXTSPLIT(TEXTJOIN(";",1,(MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(d,SEQUENCE(1+b-a,,a),f,FILTER(d,MONTH(d)+DAY(d)=--TEXT(d,"yy")),TEXTJOIN(",",1,COUNT(f),TAKE(f,1),TAKE(f,-1))))))),",",";")

8

LinkedIn Post by:
Zoran Milokanović

Listing the Count, Min and Max dates w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.FromRows(List.Transform(Table.ToRows(Source), each let l = ([List.Select](http://list.select/)(List.Transform({Number.From(_{0})..Number.From(_{1})}, each Date.From(_)), each Date.Month(_) + [Date.Day](http://date.day/)(_) = Number.Mod(Date.Year(_), 100))) in {List.Count(l), l{0}, List.Max(l)}), {"Count", "Min Date", "Max Date"})
in
Solution
9

LinkedIn Post by:
Bo Rydobon

M-Code 
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.FromRecords(Table.TransformRows(Source, each let l = [List.Select](http://list.select/)({Number.From([From Date])..Number.From([To Date])}, each let d =Date.From(_)
in [Date.Day](http://date.day/)(d)+Date.Month(d)= Number.Mod(Date.Year(d),100)) in [Count = List.Count(l),Min Date = Date.From(List.Min(l)),Max Date = Date.From(List.Max(l))]))
in
Ans
10

LinkedIn Post by:
Bo Rydobon
 

=REDUCE({"Count","Min Date","Max Date"},A2:A6,LAMBDA(a,v,LET(s,SEQUENCE(VLOOKUP(v,A2:B6,2,)-v+1,,v),f,FILTER(s,DAY(s)+MONTH(s)=MOD(YEAR(s),100)),
VSTACK(a,HSTACK(ROWS(f),MIN(f),MAX(f))))))
11

LinkedIn Post by:
Guillermo Arroyo

Power Query 
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = Table.TransformColumnTypes(Origen,{{"From Date", type date}, {"To Date", type date}}),
b = Table.AddColumn(a, "Dates", each List.Dates( [From Date], Duration.Days([To Date]-[From Date])+1, [hashtag#duration](https://www.linkedin.com/feed/hashtag/duration/)(1,0,0,0))),
c = Table.TransformColumns(b, {"Dates", (x)=> List.Select(x, each Date.Day(_)+Date.Month(_) = Number.Mod(Date.Year(_),100))}),
d = Table.AddColumn(c, "Count", each List.Count([Dates])),
e = Table.AddColumn(d, "Min Date", each List.Min([Dates])),
f = Table.AddColumn(e, "Max Date", each List.Max([Dates])),
g = Table.SelectColumns(f,{"Count", "Min Date", "Max Date"})
in
g

image
image736×157 38.2 KB

12

LinkedIn Post by:
Sunny Baggu
 

=DROP(
 REDUCE(
 "Thanks EXcel BI🌻",
 SEQUENCE(ROWS(A2:A6)),
 LAMBDA(a, v,
 VSTACK(
 a,
 LET(
 _list, SEQUENCE(INDEX(B2:B6, v, 1) - INDEX(A2:A6, v, 1) + 1, , INDEX(A2:A6, v, 1)),
 _day, DAY(_list),
 _month, MONTH(_list),
 _year, YEAR(_list),
 _cond, _day + _month - RIGHT(_year, 2) * 1,
 _flist, FILTER(_list, _cond = 0),
 HSTACK(ROWS(_flist), MIN(_flist), MAX(_flist))
 )
 )
 )
 ),
 1
)
13

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

=REDUCE(HSTACK("Count","Min Date","Max Date"),A2:A6,LAMBDA(a,v,LET(d,SEQUENCE(OFFSET(v,,1)-v+1,,v),
r,FILTER(d,DAY(d)+MONTH(d)=YEAR(d)-2000),VSTACK(a,HSTACK(ROWS(r),MIN(r),MAX(r))))))
14

LinkedIn Post by:
Kris Jaganah

=DROP(REDUCE(0,MAP(A2:A6,B2:B6,LAMBDA(x,y,LET(a,TEXT(SEQUENCE(y-x+1,,x),"dd/mm/yyyy"),b,--(--LEFT(a,2)+MID(a,4,2)=--RIGHT(a,2)),c,FILTER(DATEVALUE(a),b),TEXTJOIN("#",1,COUNT(c),TEXT(MIN(c),"dd/mm/yyyy"),TEXT(MAX(c),"dd/mm/yyyy"))))),LAMBDA(v,w,VSTACK(v,--TEXTSPLIT(w,"#")))),1)

15

LinkedIn Post by:
Daniel G.

=LET(c,A2:B6,fx,LAMBDA(d,LET(f,INDEX(d,1,1),s,SEQUENCE(INDEX(d,1,2)-f+1,,f),m,MAP(s,LAMBDA(x,(DAY(x)+MONTH(x))=--RIGHT(YEAR(x),2))),HSTACK(SUM(--m),INDEX(s,XMATCH(TRUE,m,,{1,-1}))))),REDUCE(fx(CHOOSEROWS(c,1)),DROP(SEQUENCE(ROWS(c)),1),LAMBDA(a,b,VSTACK(a,fx(CHOOSEROWS(c,b))))))

16

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

=--TEXTSPLIT(CONCAT(BYROW(A2:B6, LAMBDA(x, LET(d, SEQUENCE(MAX(x)-MIN(x)+1,,MIN(x)), f, FILTER(d, DAY(d)+MONTH(d)=--TEXT(d, "yy")), TEXTJOIN(",",,COUNT(f), MIN(f), MAX(f), "/"))))),",","/",1)

17

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=REDUCE({"Count";"Min Date";"Max Date"}, MAP(A2:A6, B2:B6, LAMBDA(i, f, LET(_s, SEQUENCE(f-i+1, ,i),_n, FILTER(_s, MAP(_s, LAMBDA(a, DAY(a)+MONTH(a)=--RIGHT(YEAR(a), 2)))), TEXTJOIN(", ", , COUNT(_n), MIN(_n), MAX(_n))))), LAMBDA(i, x, VSTACK(i, --TEXTSPLIT(x,", "))))

18

LinkedIn Post by:
Brian Julius

My #powerquery

Solution 
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"To Date", type date}, {"From Date", type date}}),
DatesTable = Table.AddColumn(ChangeType, "DateList", each {Number.From( [From Date])..Number.From([To Date])}),
Expand = Table.ExpandListColumn(DatesTable, "DateList"),
ReType = Table.TransformColumnTypes(Expand,{{"DateList", type date}}),
#"AddMM+DD" = Table.AddColumn(ReType, "MM+DD", each Date.Month([DateList]) + Date.Day([DateList])),
AddYY = Table.AddColumn(#"AddMM+DD", "YY", each Number.From(Text.End( Date.ToText([DateList]), 2))),
FilterEqual = Table.SelectRows( Table.AddColumn(AddYY, "Equal", each if [#"MM+DD"] = [YY] then 1 else 0), each [Equal] = 1),
Group = Table.Group(FilterEqual, {"From Date", "To Date"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Min Date", each List.Min([DateList]), type nullable date}, {"Max Date", each List.Max([DateList]), type nullable date}})
in
Group
19

LinkedIn Post by:
Lorenzo Foti
 

=+LET(
 date1;A2;
 date2;B2;
 listLng;B2-A2;
 dates;date1+SEQUENCE(listLng);
 checks;IF(DAY(dates)+MONTH(dates)=NUMBERVALUE(RIGHT(YEAR(dates);2));1;0);
 cnt;SUM(checks);
 minDate;TAKE(FILTER(dates;checks=1);1);
 maxDate;TAKE(FILTER(dates;checks=1);-1);
 outP;HSTACK(cnt;minDate;maxDate);
outP)
20

LinkedIn Post by:
Tolga Demirci

=HSTACK(MAP(A2:A6;B2:B6;LAMBDA(a;b;LET(x;SEQUENCE(b-a+1;;a;1);COUNTA(FILTER(IF(DAY(x)+MONTH(x)=VALUE(RIGHT(YEAR(x);2));x;"");IF(DAY(x)+MONTH(x)=VALUE(RIGHT(YEAR(x);2));x;"")<>"")))));MAP(A2:A6;B2:B6;LAMBDA(x;y;LET(p;SEQUENCE(y-x+1;;x;1);MIN(IF(DAY(p)+MONTH(p)=VALUE(RIGHT(YEAR(p);2));p;"")))));MAP(A2:A6;B2:B6;LAMBDA(m;n;LET(z;SEQUENCE(n-m+1;;m;1);MAX(IF(DAY(z)+MONTH(z)=VALUE(RIGHT(YEAR(z);2));z;""))))))