Populate all months of the year. For missing months IN A QUARTER – 1. Fill down from previous month’s value (see Feb) 2. Fill up only if previous month’s value not available (see Apr) 3. 0 if no value is available (see Oct to Dec)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 698
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Fill Missing Monthly Values with Power Query
Power Query solution 1 for Fill Missing Monthly Values, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Quarter = List.Transform({1..12}, each Date.QuarterOfYear(hashtag#date(2025,_,1))),
Month = List.Transform({1..12}, each Text.Start(Date.MonthName(hashtag#date(2025,_,1), "En-US"),3)),
Match = Table.AddColumn(Table.FromColumns({Quarter, Month}, {"Quarter", "Month"}), "Amount", (x)=>
Table.SelectRows(Source, each [Month] = x[Month])[Amount]{0}?),
Tbl = Table.Combine(Table.Group(Match, {"Quarter"}, {{"A", each
let
a = _,
b = Table.AddIndexColumn(_, "Idx"),
c = Table.AddColumn(b, "A", each if b[Amount]{[Idx]} = null then try b[Amount]{[Idx]-1} otherwise null else b[Amount]{[Idx]}),
d = Table.AddColumn(c[[Month], [A], [Idx]], "Amount", each if c[A]{[Idx]} = null then try c[A]{[Idx]+1} otherwise null else c[A]{[Idx]})
in d[[Month], [Amount]]}})[A]),
Sol = Table.ReplaceValue(Tbl,null,0,Replacer.ReplaceValue,{"Amount"})
in
Sol
Power Query solution 2 for Fill Missing Monthly Values, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(
Tabela1,
"Personalizar",
each Date.QuarterOfYear(Date.From("1-" & [Month]))
),
tab = Table.FromRows(
{
{"Jan", 1},
{"Feb", 1},
{"Mar", 1},
{"Apr", 2},
{"May", 2},
{"Jun", 2},
{"Jul", 3},
{"Aug", 3},
{"Sep", 3},
{"Oct", 4},
{"Nov", 4},
{"Dec", 4}
},
{"M", "Q"}
),
join = Table.Join(tab, {"M"}, Fonte, {"Month"}, JoinKind.FullOuter),
grp = Table.Group(
join,
{"Q"},
{
{
"tab",
each
let
a = Table.FillDown(_, {"Amount"}),
b = Table.ReplaceValue(a, null, 0, Replacer.ReplaceValue, {"Amount"})
in
b[[M], [Amount]]
}
}
)[tab],
rst = Table.Combine(grp)
in
rstPower Query solution 3 for Fill Missing Monthly Values, proposed by Antriksh Sharma:
let
Source = Table.TransformColumns ( Table, { "Month", each Date.FromText ( _ & "/1" ), type date } ),
A = List.Generate ( () => hashtag#date ( 2025, 01, 01), each _ <= hashtag#date ( 2025, 12, 31), each Date.AddMonths ( _ , 1 ) ),
B = let a = List.Difference ( A, Source[Month] ), b = List.Repeat ( {null}, List.Count ( a ) ) in Table.FromColumns ( { a, b }, type table [Month = date, Amount = Int64.Type] ),
C = Table.Sort ( Source & B, { "Month", Order.Ascending } ),
D = Table.Group ( C, "Month", { "T", each Table.ReplaceValue ( Table.FillUp ( Table.FillDown ( _, { "Amount" } ), { "Amount" } ), each null, 0, Replacer.ReplaceValue, { "Amount" } ) }, 0, (x, y) => Byte.From ( Date.QuarterOfYear ( x ) <> Date.QuarterOfYear ( y ) ) ),
E = Table.TransformColumns ( Table.Combine ( D[T] ), { "Month", each Text.Start ( Date.MonthName ( _ ), 3 ), type text } )
in
E
Power Query solution 4 for Fill Missing Monthly Values, proposed by Antriksh Sharma:
let
Source = Table.AddColumn(
Table,
"MonthNo",
each Date.Month(Date.FromText([Month] & "/1")),
Int64.Type
),
A = List.Transform(Source[Month], each Date.Month(Date.FromText("1-" & _))),
B = Table.FromRows(
List.Transform(
List.Difference({1 .. 12}, A),
each {
Text.Start(Date.MonthName(Date.FromText(Text.From(_) & "/1")), 3),
null,
Date.Month(Date.FromText(Text.From(_) & "/1"))
}
),
type table [Month = text, Amount = Int64.Type, MonthNo = Int64.Type]
),
C = Table.Group(
Table.Sort(Source & B, {"MonthNo", Order.Ascending}),
"MonthNo",
{
"T",
each Table.ReplaceValue(
Table.FillUp(Table.FillDown(_, {"Amount"}), {"Amount"}),
each null,
0,
Replacer.ReplaceValue,
{"Amount"}
)
},
GroupKind.Local,
(x, y) => Byte.From(Number.RoundUp(x / 3, 0) <> Number.RoundUp(y / 3, 0))
),
D = Table.RemoveColumns(Table.Combine(C[T]), "MonthNo")
in
DPower Query solution 5 for Fill Missing Monthly Values, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = [
a = Table.FromRows( List.Generate(
()=>[x= 1, y = hashtag#date(2025,1,1)], each [x]<=12, each [x=[x]+1, y = Date.AddMonths([y],1)], each {Date.ToText([y],[Format = "MMM"])}
),{"Month"}),
b = Table.AddColumn(a,"Amount", (te)=> try Table.SelectRows( Source, (ti)=>ti[Month]=te[Month])[Amount]{0} otherwise 0)
][b],
grup = Table.Combine( Table.Group(a, {"Month"}, {{"r", each [
amt = [Amount],
g1 = List.Transform({0..List.Count(amt)-1}, (x)=> if x= 0 and amt{x}=0 then amt{x+1} else if amt{x}=0 then amt{x-1} else amt{x}),
tbl = Table.FromColumns({[Month],g1},Table.ColumnNames(_))
][tbl]
}}, GroupKind.Local,
(x,y)=> Value.Compare(
Date.QuarterOfYear(Date.FromText(x[Month], [Format = "MMM"])),
Date.QuarterOfYear(Date.FromText(y[Month], [Format = "MMM"]))
)
)[r])
in
grup
Power Query solution 6 for Fill Missing Monthly Values, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name="tblStart"]}[Content],
tbl = hashtag#table(type table [ID = Int64.Type, Mon = text, Qtr = Int64.Type],
{
{1, "Jan", 1},
{2, "Feb", 1},
{3, "Mar", 1},
{4, "Apr", 2},
{5, "May", 2},
{6, "Jun", 2},
{7, "Jul", 3},
{8, "Aug", 3},
{9, "Sep", 3},
{10, "Oct", 4},
{11, "Nov", 4},
{12, "Dec", 4}
}
),
A = Table.Join(tbl, "Mon", source, "Month", JoinKind.LeftOuter),
B = Table.Combine(Table.Group(A, {"Qtr"}, {{"All", each Table.ReplaceValue(Table.FillUp(Table.FillDown(Table.Sort([[ID], [Mon], [Amount]], {{"ID", Order.Ascending}}), {"Amount"}), {"Amount"}), null, 0, Replacer.ReplaceValue, {"Amount"})}})[All])[[Mon], [Amount]]
in
B
Power Query solution 7 for Fill Missing Monthly Values, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl =
Table.Join(
Table.TransformColumns( Source, { "Month", each Date.From( _ & " 2025" ) } ), "Month",
Table.FromList( List.Transform( { 1..12 }, each hashtag#date( 2025, _, 1 ) ), Splitter.SplitByNothing(), { "D" } ),
"D", JoinKind.RightOuter
),
Fx = each
[
a = "Amount",
b = Table.FillUp( Table.FillDown( _, { a } ), { a } ),
c = Table.TransformColumns( b, { "D", each Text.Start( Date.MonthName( _, "en-US" ), 3 ) } ),
d = Table.ReplaceValue( c, null, 0, Replacer.ReplaceValue, { a } ) [[D],[Amount]]
][d],
Grp =
Table.Group(
Table.Sort( Tbl, "D" ), "D", { "B", Fx },
0, ( x, y ) => Value.Compare( Date.StartOfQuarter( x ), Date.StartOfQuarter( y ) )
)[B],
Res = Table.RenameColumns( Table.Combine( Grp ), { "D", "Month" } )
in
Res
Solving the challenge of Fill Missing Monthly Values with Excel
Excel solution 1 for Fill Missing Monthly Values, proposed by Bo Rydobon 🇹🇭:
=LET(n,MONTH(A3:A7&1),s,SEQUENCE(12),HSTACK(TEXT(s*29,"mmm"),MAP(s,LAMBDA(m,LET(y,INT((n+2)/3)=INT((m+2)/3),IFNA(LOOKUP(9^9,XLOOKUP(m,n/y,B3:B7/y,,{1,-1})),))))))Excel solution 2 for Fill Missing Monthly Values, proposed by 🇰🇷 Taeyong Shin:
=LET(
s,
SEQUENCE(
12
),
a,
B3:B7,
q,
MONTH(
s*10
),
m,
MONTH(
A3:A7&-1
),
r,
MONTH(
m*10
),
HSTACK(
TEXT(
s&-1,
"mmm"
),
CHOOSE(
MMULT(
IFNA(
XMATCH(
q,
r,
,
{1,
-1}
),
{1,
3}
),
{-1;1}
)+1,
LOOKUP(
q,
r,
a
),
LOOKUP(
q&s,
r&m,
a
),
0
)
)
)Excel solution 3 for Fill Missing Monthly Values, proposed by Kris Jaganah:
=LET(
a,
SEQUENCE(
12
),
b,
CEILING(
a/3,
1
),
c,
TEXT(
a*28,
"mmm"
),
d,
VLOOKUP(
c,
A3:B7,
2,
),
HSTACK(
VSTACK(
"Month",
c
),
REDUCE(
"Amount",
UNIQUE(
b
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
d,
b=y
),
SCAN(
IFNA(
INDEX(
f,
2,
),
),
f,
LAMBDA(
x,
y,
IFNA(
y,
x
)
)
)
)
)
)
)
)
)Excel solution 4 for Fill Missing Monthly Values, proposed by Kris Jaganah:
=LET(
a,
SEQUENCE(
12
),
b,
TEXT(
a*28,
"mmm"
),
c,
VLOOKUP(
b,
A3:B7,
2,
),
HSTACK(
b,
IFNA(
IFNA(
c,
IF(
MOD(
a,
3
)=1,
INDEX(
c,
a+1
),
INDEX(
c,
a-1
)
)
),
)
)
)Excel solution 5 for Fill Missing Monthly Values, proposed by Julian Poeltl:
=LET(A,B3:B7,M,MONTH(--("1."&A3:A7)),Q,ROUNDUP(M/3,),REDUCE(HSTACK("Month","Amount"),SEQUENCE(12),LAMBDA(H,B,LET(R,ROUNDUP(B/3,),VSTACK(H,HSTACK(TEXT(EOMONTH(0,B-1),"MMM"),XLOOKUP(B&R,M&Q,A,XLOOKUP(B-1&R,M&Q,A,XLOOKUP(B+1&R,M&Q,A,0)))))))))Excel solution 6 for Fill Missing Monthly Values, proposed by Oscar Mendez Roca Farell:
=LET(
r,
ROW(
1:12
),
m,
TEXT(
1&-r,
"b1mmm"
),
v,
XLOOKUP(
m,
A3:A7,
B3:B7,
0
),
HSTACK(
m,
IF(
v,
v,
IF(
MOD(
r,
3
)=1,
DROP(
v,
1
),
INDEX(
v,
r-1
)
)
)
)
)Excel solution 7 for Fill Missing Monthly Values, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
12
),
_m,
TEXT(
DATE(
2025,
_s,
1
),
"mmm"
),
_a,
XLOOKUP(
A3:A7,
_m,
_s
),
_b,
WRAPCOLS(
XLOOKUP(
_s,
_a,
_a,
""
),
3
),
_c,
DROP(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
a,
v,
HSTACK(
a,
LET(
_c,
INDEX(
_b,
,
v
),
SCAN(
"",
_c,
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
)
)
)
)
),
,
1
),
_d,
VSTACK(
IF(
INDEX(
_c,
1,
) = "",
INDEX(
_c,
2,
),
INDEX(
_c,
1,
)
),
DROP(
_c,
1
)
),
_e,
TOCOL(
XLOOKUP(
_d,
_a,
B3:B7,
0
),
,
1
),
HSTACK(
_m,
_e
)
)Excel solution 8 for Fill Missing Monthly Values, proposed by LEONARD OCHEA 🇷🇴:
=LET(a,
A3:A7,
b,
B3:B7,
X,
XLOOKUP,
s,
SEQUENCE(
12
),
q,
INT((s+2)/3),
w,
INT((MONTH(
1&a
)+2)/3),
m,
TEXT(
"1/"&s,
"mmm"
),
r,
MOD(
s-1,
3
)+1,
HSTACK(
m,
MAP(
m,
q,
r,
LAMBDA(
i,
j,
k,
X(
i,
a,
b,
X(
j,
w,
b,
0,
,
IF(
k=3,
-1,
1
)
)
)
)
)
))Excel solution 9 for Fill Missing Monthly Values, proposed by Md. Zohurul Islam:
=LET(
u,
A3:A7,
v,
B3:B7,
sq,
DATE(
2025,
SEQUENCE(
12
),
1
),
m,
TEXT(
sq,
"mmm"
),
qtr,
& CEILING(
MONTH(
sq
)/12*4,
1
),
w,
REDUCE(
{"Month",
"Amount"},
UNIQUE(
qtr
),
LAMBDA(
x,
y,
LET(
a,
FILTER(
m,
qtr=y,
0
),
b,
ISNUMBER(
XMATCH(
u,
a
)
),
c,
FILTER(
HSTACK(
u,
v
),
b
),
d,
XLOOKUP(
a,
TAKE(
c,
,
1
),
DROP(
c,
,
1
),
0
),
e,
SCAN(
0,
d,
LAMBDA(
x,
y,
IF(
y=0,
x,
y
)
)
),
f,
FILTER(
HSTACK(
a,
e
),
e
),
g,
XLOOKUP(
a,
TAKE(
f,
,
1
),
DROP(
f,
,
1
),
,
1
),
h,
IFERROR(
HSTACK(
a,
g
),
0
),
i,
VSTACK(
x,
h
),
i
)
)
),
w
)Excel solution 10 for Fill Missing Monthly Values, proposed by Hamidi Hamid:
=LET(y,TEXT("01/"&SEQUENCE(12),"mmm"),v,XLOOKUP(y,A3:A7,B3:B7,0),ev,VSTACK(DROP(v,1),0),s,ROUNDUP(SEQUENCE(12)/3,0),sq,VSTACK(DROP(s,1),0),g,DROP(VSTACK(0,(sq=s)*(ev=0)*v),-1)+v,rt,IF(g>0,g,XLOOKUP(s,s,g,,,-1)),HSTACK(y,rt))Excel solution 11 for Fill Missing Monthly Values, proposed by Asheesh Pahwa:
=LET(t,TEXT(SEQUENCE(12)*30,"mmm"),x,XLOOKUP(t,A3:A7,B3:B7,0),w,WRAPCOLS(x,3),I,INDEX(w,1,),v,VSTACK(IF(I=0,INDEX(w,2,),I),DROP(w,1)),
r,DROP(REDUCE("",SEQUENCE(4),LAMBDA(x,y,HSTACK(x,LET(I,INDEX(v,,y),
SCAN(0,I,LAMBDA(a,v,IF(v,v,a))))))),,1),HSTACK(t,TOCOL(r,,1)))Excel solution 12 for Fill Missing Monthly Values, proposed by ferhat CK:
=LET(b,TEXT(EOMONTH("1.1.25",SEQUENCE(12,,0)),"mmm"),c,XLOOKUP(b,A3:A7,B3:B7,0),d,WRAPCOLS(c,3),HSTACK(b,DROP(REDUCE(0,SEQUENCE(4),LAMBDA(x,y,VSTACK(x,LET(q,SCAN(0,CHOOSECOLS(d,y),LAMBDA(a,v,IF(v=0,a,v))),IF(q=0,MAX(q),q))))),1)))Excel solution 13 for Fill Missing Monthly Values, proposed by Mey Tithveasna:
=LET(
a,
A3:A7,
b,
B3:B7,
s,
SEQUENCE(
12
)
m,
TEXT(
s*29,
"mmm"
),
c,
XLOOKUP(
m,
a,
b,
,
1
),
VSTACK(
A2:B2,
HSTACK(
m,
c
)
)
)Excel solution 14 for Fill Missing Monthly Values, proposed by Fredson Alves Pinho:
=LET(
x,
TEXT(
SEQUENCE(
4,
3
)*30,
"mmm"
),
a,
XLOOKUP(
x,
A3:A7,
B3:B7,
0
),
b,
IF(
a,
a,
CHOOSECOLS(
a,
{3,
1,
2}
)
),
HSTACK(
TOCOL(
x
),
TOCOL(
IF(
b,
b,
CHOOSECOLS(
a,
{2,
3,
1}
)
)
)
)
)Excel solution 15 for Fill Missing Monthly Values, proposed by Craig Runciman:
=LET(I,INDEX,mnth,TEXT(SEQUENCE(12)*30,"mmm"),rr,IFERROR(I(B3:B7,XMATCH(mnth,A3:A7,0)),0),x,WRAPCOLS(rr,3),HSTACK(mnth,TOCOL(IF(I(x,{1;2;3},{1,2,3,4})=0,I(x,{2;1;2},{1,2,3,4}),x),,1)))Excel solution 16 for Fill Missing Monthly Values, proposed by red craven:
=REDUCE(A2:B2,
ROW(
1:4
),
LAMBDA(a,
v,
LET(n,
{1;2;3},
m,
TEXT((3*(v-1)+n)*28,
"mmm"),
X,
XLOOKUP,
z,
X(
m,
A3:A7,
B3:B7,
0
),
k,
n/(z>0),
VSTACK(
a,
HSTACK(
m,
X(
n,
k,
z,
X(
n,
k,
z,
,
1
),
-1
)
)
))))Solving the challenge of Fill Missing Monthly Values with Python
Python solution 1 for Fill Missing Monthly Values, proposed by Konrad Gryczan, PhD:
import pandas as pd
from calendar import month_abbr
path = "697 Fill up or down.xlsx"
input_df = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=6)
test_df = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=13).rename(columns=lambda col: col.split('.')[0])
months_df = pd.DataFrame({
'Month': list(month_abbr)[1:13],
'month_num': range(1, 13),
'Quarter': ['Q' + str((i-1)//3 + 1) for i in range(1, 13)]
})
df = months_df.merge(input_df, on='Month', how='left')
df = df.sort_values('month_num')
df['Amount'] = df.groupby('Quarter')['Amount'].transform(lambda x: x.ffill().bfill()).fillna(0).astype(int)
df = df[['Month', 'Amount']].reset_index(drop=True)
print(df.equals(test_df)) # True
Python solution 2 for Fill Missing Monthly Values, proposed by Luan Rodrigues:
import pandas as pd
file = r"Excel_Challenge_697 - Fill up or down.xlsx"
df = pd.read_excel(file, usecols='A:B',skiprows=1).dropna()
meses = {
'Jan': '1', 'Feb': '1', 'Mar': '1',
'Apr': '2', 'May': '2', 'Jun': '2',
'Jul': '3', 'Aug': '3', 'Sep': '3',
'Oct': '4', 'Nov': '4', 'Dec': '4'
}
df['Data'] = df['Month'].map(meses)
df_month = pd.DataFrame(list(meses.items()), columns=['M', 'Q']).reset_index()
merge = pd.merge(df_month, df,left_on='M',right_on='Month',how='outer').sort_values(by='index')
grp = merge.groupby('Q').apply(lambda x: x.assign(Amount=x['Amount'].ffill().bfill()))
grp = grp[['M','Amount']].reset_index(drop=True)
grp['Amount'] = grp['Amount'].fillna(0)
print(grp)
Solving the challenge of Fill Missing Monthly Values with Python in Excel
Python in Excel solution 1 for Fill Missing Monthly Values, proposed by Alejandro Campos:
import calendar
df = xl("A2:B7", headers=True)
all_months = list(calendar.month_abbr[1:])
q = dict(zip(all_months, [1]*3+[2]*3+[3]*3+[4]*3))
df = pd.merge(pd.DataFrame({'Month': all_months}), df, on='Month', how='left')
df['Quarter'] = df['Month'].map(q)
df = df.groupby('Quarter', group_keys=False).apply(lambda g: g.fillna(method='ffill').fillna(method='bfill').fillna(0))
df = df.drop(columns='Quarter')
Python in Excel solution 2 for Fill Missing Monthly Values, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:B7", True)
df["m"] = pd.to_datetime(df["Month"], format="%b").dt.month
result = (
pd.DataFrame({"m": range(1, 13)})
.assign(Month=lambda x: pd.to_datetime(x["m"], format="%m").dt.strftime("%b"))
.merge(df[["m", "Amount"]], on="m", how="left")
.assign(q=lambda x: ((x["m"] - 1) // 3) + 1)
.assign(
Amount=lambda x: x.groupby("q")["Amount"].transform(
lambda s: s.ffill().bfill().fillna(0)
)
)[["Month", "Amount"]]
)
Solving the challenge of Fill Missing Monthly Values with R
R solution 1 for Fill Missing Monthly Values, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/697 Fill up or down.xlsx"
input = read_excel(path, range = "A2:B7")
test = read_excel(path, range = "D2:E14")
month_abbr = data.frame(month_abbr = month.abb[1:12])
df = month_abbr %>%
left_join(input, by = c("month_abbr" = "Month")) %>%
mutate(Quarter = paste0("Q", ceiling(match(month_abbr, month.abb) / 3))) %>%
group_by(Quarter) %>%
fill(Amount, .direction = "downup") %>%
ungroup() %>%
replace_na(list(Amount = 0)) %>%
rename(Month = month_abbr) %>%
select(-Quarter)
all.equal(df, test)
# TRUE
&&