Merge both the tables. Delivery dates will be populated against calendar date for +1 and -1 dates also apart from matched rows. If there is a tie when populating for +1 and -1 dates, then immediate matched row will take precedence. This case is illustrated by 25-May-24 and 26-May-24.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 186
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Match Deliveries with Calendar Dates with Power Query
Power Query solution 1 for Match Deliveries with Calendar Dates, proposed by Bo Rydobon 🇹🇭:
let
Source = Table.Join(
Excel.CurrentWorkbook(){[Name = "Calendar"]}[Content],
"Calendar Date",
Excel.CurrentWorkbook(){[Name = "Delivery"]}[Content],
"Delivery Date",
1
),
Fn = (a) =>
List.Transform(
List.Zip({a, {null} & List.RemoveLastN(a, 1), List.Skip(a) & {null}}),
each _{0} ?? _{1} ?? _{2}
),
Ans = Table.FromColumns(
{Source[Calendar Date], Fn(Source[Delivery Date]), Fn(Source[Vendor])},
Table.ColumnNames(Source)
)
in
AnsPower Query solution 2 for Match Deliveries with Calendar Dates, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T1 = Source("Table1"),
T2 = Source("Table2"),
H = Table.ColumnNames,
S = Table.FromRows(
List.Transform(
T1[Calendar Date],
each List.Select(
List.Accumulate(
Table.ToRows(T2),
{},
(s, c) =>
let
d = List.Transform({- 1, 1}, each Date.AddDays(c{0}, _)),
f = each {{{_} & c}, {}}{Number.From(List.Contains(T2[Delivery Date], _))}
in
s & f(d{0}) & {{c{0}} & c} & f(d{1})
),
(r) => r{0} = _
){0}?
?? {_, null, null}
),
H(T1) & H(T2)
)
in
SPower Query solution 3 for Match Deliveries with Calendar Dates, proposed by Kris Jaganah:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
DelDate = Table.AddColumn(
T1,
"Ans",
each
let
a = [Calendar Date],
b = T2[Delivery Date]{List.PositionOf(T2[Delivery Date], a)},
c = List.First(List.Select(T2[Delivery Date], each _ >= (Date.AddDays(a, - 1)))),
d = try b otherwise c,
e = try if Duration.Days(d - a) > 1 then null else d otherwise null,
f = try T2[Vendor]{List.PositionOf(T2[Delivery Date], e)} otherwise null,
g = Record.FromList({e, f}, type [Delivery Date = date, Vendor = text])
in
g
),
Xp = Table.ExpandRecordColumn(DelDate, "Ans", {"Delivery Date", "Vendor"})
in
XpPower Query solution 4 for Match Deliveries with Calendar Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
CalendarDate = Excel.CurrentWorkbook(){[Name = "CalendarDate"]}[Content],
DeliveryDate = Excel.CurrentWorkbook(){[Name = "DeliveryDate"]}[Content],
Record = Table.AddColumn(
CalendarDate,
"R",
each DeliveryDate{[Delivery Date = [Calendar Date]]}?
?? [
T = Table.SelectRows(
DeliveryDate,
(f) =>
[Calendar Date]
= Date.AddDays(f[Delivery Date], - 1) or [Calendar Date]
= Date.AddDays(f[Delivery Date], 1)
),
C = Table.RowCount(T),
D1 = Table.FirstValue(T),
D2 = Text.Combine(
List.Transform(T[Delivery Date], (f) => DateTime.ToText(f, "dd-MM-yyyy")),
", "
),
R = [Delivery Date = if C = 1 then D1 else D2, Vendor = Text.Combine(T[Vendor], ", ")]
][R]
),
Return = Table.ExpandRecordColumn(Record, "R", Table.ColumnNames(DeliveryDate))
in
ReturnPower Query solution 5 for Match Deliveries with Calendar Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
CalendarDate = Excel.CurrentWorkbook(){[Name = "CalendarDate"]}[Content],
DeliveryDate = Excel.CurrentWorkbook(){[Name = "DeliveryDate"]}[Content],
Record = Table.AddColumn(
CalendarDate,
"R",
each DeliveryDate{[Delivery Date = [Calendar Date]]}?
?? DeliveryDate{[Delivery Date = Date.AddDays([Calendar Date], - 1)]}?
?? DeliveryDate{[Delivery Date = Date.AddDays([Calendar Date], 1)]}?
),
Return = Table.ExpandRecordColumn(Record, "R", Table.ColumnNames(DeliveryDate))
in
ReturnPower Query solution 6 for Match Deliveries with Calendar Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
CalendarDate = Excel.CurrentWorkbook(){[Name = "CalendarDate"]}[Content],
DeliveryDate = Excel.CurrentWorkbook(){[Name = "DeliveryDate"]}[Content],
Same = Table.DuplicateColumn(DeliveryDate, "Delivery Date", "D"),
Less1Day = Table.AddColumn(DeliveryDate, "D", each Date.AddDays([Delivery Date], - 1)),
Add1Day = Table.AddColumn(DeliveryDate, "D", each Date.AddDays([Delivery Date], 1)),
Combine = Table.Distinct(Same & Less1Day & Add1Day, "D"),
Join = Table.AddJoinColumn(CalendarDate, {"Calendar Date"}, Combine, {"D"}, "J"),
Expand = Table.ExpandTableColumn(Join, "J", Table.ColumnNames(DeliveryDate)),
Return = Table.Sort(Expand, "Calendar Date")
in
ReturnPower Query solution 7 for Match Deliveries with Calendar Dates, proposed by Eric Laforce:
let
TD = Table.Buffer(Excel.CurrentWorkbook(){[Name = "tData186b"]}[Content]),
Source = Excel.CurrentWorkbook(){[Name = "tData186"]}[Content],
Merge = Table.AddColumn(
Source,
"M",
each
let
cd = [Calendar Date],
s1 = Table.SelectRows(TD, each [Delivery Date] = cd),
s2 = Table.SelectRows(
TD,
each List.Contains({Date.AddDays(cd, - 1), Date.AddDays(cd, + 1)}, [Delivery Date])
)
in
Table.FirstN(Table.Combine({s1, s2}), 1)
),
Expand = Table.ExpandTableColumn(Merge, "M", Table.ColumnNames(TD))
in
ExpandPower Query solution 8 for Match Deliveries with Calendar Dates, proposed by Peter Tholstrup:
let
get_source = (t) => Excel.CurrentWorkbook(){[Name = t]}[Content],
calendar = get_source("Table1"),
deliveries = get_source("Table2"),
get_match = (d) =>
[
adjacent = List.Transform({- 1, 1}, each Date.AddDays(d, _)),
select = (criteria) => Table.SelectRows(deliveries, criteria),
result = Table.Combine(
{select(each [Delivery Date] = d), select(each List.Contains(adjacent, _[Delivery Date]))}
){0}?
][result],
match = Table.AddColumn(calendar, "temp", each get_match([Calendar Date])),
result = Table.ExpandRecordColumn(match, "temp", Table.ColumnNames(deliveries))
in
resultPower Query solution 9 for Match Deliveries with Calendar Dates, proposed by Venkata Rajesh:
let
Source = Calendar,
Output = Table.AddColumn(
Source,
"Record",
each [
fx = (n) => try Delivery{[#"Delivery Date" = Date.AddDays([Calendar Date], n)]} otherwise null,
x = fx(0) ?? fx(- 1) ?? fx(1)
][x]
),
Expand = Table.ExpandRecordColumn(Output, "Record", {"Delivery Date", "Vendor"})
in
ExpandPower Query solution 10 for Match Deliveries with Calendar Dates, proposed by Venkata Rajesh:
let
Source = Calendar,
Output = Table.AddColumn(
Source,
"Record",
each [
fx = (n) => Delivery{[#"Delivery Date" = Date.AddDays([Calendar Date], n)]},
x = try fx(0) otherwise try fx(- 1) otherwise try fx(1) otherwise null
][x]
),
Expand = Table.ExpandRecordColumn(Output, "Record", {"Delivery Date", "Vendor"})
in
ExpandSolving the challenge of Match Deliveries with Calendar Dates with Excel
Excel solution 1 for Match Deliveries with Calendar Dates, proposed by Bo Rydobon 🇹🇭:
=LET(c,
A2:A30,
d,
C2:C7,
IFNA(HSTACK(c,
INDEX(C2:D7,
IFNA(XMATCH(
c,
d
),
(XMATCH(
c,
TOCOL(
d+{-1,
1}
)
)+1)/2),
{1,
2})),
""))Excel solution 2 for Match Deliveries with Calendar Dates, proposed by محمد حلمي:
=LET(
c,
C2:C7,
i,
LAMBDA(
x,
MAP(
A2:A30,
LAMBDA(
a,
INDEX(
x,
LOOKUP(
9,
XMATCH(
a+{-1,
1,
0},
c
)
)
)
)
)
),
IFNA(
HSTACK(
i(
c
),
i(
D2:D7
)
),
""
)
)Excel solution 3 for Match Deliveries with Calendar Dates, proposed by Kris Jaganah:
=LET(
a,
A2:A30,
b,
C2:C7,
c,
XLOOKUP(
a+1,
b,
b,
,
-1
),
d,
IFS(
c-XLOOKUP(
a,
b,
b,
0
)=1,
a,
a-c>1,
"",
1,
c
),
HSTACK(
a,
d,
XLOOKUP(
d,
b,
D2:D7,
""
)
)
)Excel solution 4 for Match Deliveries with Calendar Dates, proposed by Julian Poeltl:
=LET(
T,
A1:A30,
TO,
DROP(
T,
1
),
TT,
C1:D7,
TTT,
DROP(
TT,
1
),
DD,
TAKE(
TTT,
,
1
),
XO,
XLOOKUP(
TO,
DD,
DD,
,
-1
),
XT,
XLOOKUP(
TO,
DD,
DD,
0,
1
),
DO,
ABS(
TO-XO
),
DT,
ABS(
TO-XT
),
DDC,
IF(
DO<2,
XO,
IF(
DT<2,
XT,
""
)
),
V,
XLOOKUP(
DDC,
DD,
TAKE(
TTT,
,
-1
),
""
),
VSTACK(
HSTACK(
TAKE(
T,
1
),
TAKE(
TT,
1
)
),
HSTACK(
TO,
DDC,
V
)
)
)Excel solution 5 for Match Deliveries with Calendar Dates, proposed by Oscar Mendez Roca Farell:
=LET(
c,
A2:A30,
d,
C2:C7,
x,
XLOOKUP(
c,
SORT(
TOCOL(
d+{0,
1,
-1}
)
),
VSTACK(
d,
d,
d
),
""
),
HSTACK(
c,
x,
XLOOKUP(
x,
d,
D2:D7,
""
)
)
)Excel solution 6 for Match Deliveries with Calendar Dates, proposed by Duy Tùng:
=LET(
a,
A2:A30,
c,
C2:C7,
b,
IFERROR(
MAP(
a,
LAMBDA(
x,
@TOCOL(
XLOOKUP(
x-{0;1;-1},
c,
c
),
3
)
)
),
""
),
HSTACK(
a,
b,
XLOOKUP(
b,
c,
D2:D7,
""
)
)
)Excel solution 7 for Match Deliveries with Calendar Dates, proposed by Sunny Baggu:
=LET(
cd,
A2:A30,
v,
cd - TOROW(
C2:C7
),
cri,
BYROW(
v,
LAMBDA(
x,
MIN(
IF(
x = {-1; 0; 1},
SEQUENCE(
,
ROWS(
C2:C7
)
)
)
)
)
),
HSTACK(
cd,
IF(
cri,
INDEX(
C2:D7,
cri,
{1,
2}
),
""
)
)
)Excel solution 8 for Match Deliveries with Calendar Dates, proposed by LEONARD OCHEA 🇷🇴:
=LET(
d,
A2:A30,
i,
C2:C7,
j,
D2:D7,
F,
LAMBDA(
x,
IFERROR(
BYROW(
XLOOKUP(
d+{0,
-1,
1},
i,
x,
),
LAMBDA(
a,
@TOROW(
a,
3
)
)
),
""
)
),
HSTACK(
d,
F(
i
),
F(
j
)
)
)
With header
=REDUCE(
F1:H1,
A2:A30,
LAMBDA(
a,
b,
LET(
F,
LAMBDA(
x,
IFERROR(
@TOCOL(
XLOOKUP(
b+{0;-1;1},
C2:C7,
x,
),
3
),
""
)
),
VSTACK(
a,
HSTACK(
b,
F(
C2:C7
),
F(
D2:D7
)
)
)
)
)
)Excel solution 9 for Match Deliveries with Calendar Dates, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A30,
v,
C2:C7,
w,
D2:D7,
hdr,
HSTACK(
A1,
C1,
D1
),
a,
XLOOKUP(
HSTACK(
u,
u+1,
u-1
),
v,
v,
""
),
b,
IFERROR(
BYROW(
a,
LAMBDA(
x,
LET(
p,
TOCOL(
x,
1
),
q,
FILTER(
p,
p<>""
),
TAKE(
q,
1
)
)
)
),
""
),
c,
XLOOKUP(
b,
v,
w,
"",
-1
),
d,
IF(
b="",
"",
c
),&
VSTACK(
hdr,
HSTACK(
u,
b,
d
)
)
)Excel solution 10 for Match Deliveries with Calendar Dates, proposed by Hamidi Hamid:
=HSTACK(
HSTACK(
A2:A30;
XLOOKUP(
A2:A30;
VSTACK(
$C$2:$C$7;
+C2:C7+1;
C2:C7-1
);
VSTACK(
$C$2:$C$7;
+C2:C7;
C2:C7
);
"";
0
)
);
XLOOKUP(
TAKE(
HSTACK(
A2:A30;
XLOOKUP(
A2:A30;
VSTACK(
$C$2:$C$7;
+C2:C7+1;
C2:C7-1
);
VSTACK(
$C$2:$C$7;
+C2:C7;
C2:C7
);
"";
0
)
);
;
-1
);
C2:C7;
D2:D7;
""
)
)Excel solution 11 for Match Deliveries with Calendar Dates, proposed by Burhan Cesur:
=IFNA(
REDUCE(
F1:H1,
A2:A30,
LAMBDA(
s,
v,
LET(
f,
XLOOKUP(
v,
$C$2:$C$7,
$C$2:$D$7,
XLOOKUP(
v-1,
$C$2:$C$7,
$C$2:$D$7,
XLOOKUP(
v+1,
$C$2:$C$7,
$C$2:$D$7,
""
)
)
),
VSTACK(
s,
HSTACK(
v,
f
)
)
)
)
),
""
)Solving the challenge of Match Deliveries with Calendar Dates with Python in Excel
Python in Excel solution 1 for Match Deliveries with Calendar Dates, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_186.xlsx'
df1 = pd.read_excel(file_path, usecols='A')
df2 = pd.read_excel(file_path, usecols='C:D', nrows=6)
# Perform data wrangling
values = []
for i in df1.index:
for j in df2.index:
if abs(df1.iat[i, 0].day - df2.iat[j, 0].day) in [0, 1]:
values.append([df1.iat[i, 0]] + list(df2.iloc[j, 0:]))
break
df = df1.merge(df, how='left').astype(str).replace('nan', '').replace('NaT', '')
print(f'nResulting data frame has {len(df)} recordsn')
df.head(15)
&&