Divide the amount across quarters falling in between From and To Date. The division needs to be done on the basis of number of days in a quarter. The amount may have some rounding error.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 188
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Allocate Amounts by Quarter Days with Power Query
Power Query solution 1 for Allocate Amounts by Quarter Days, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
D = (t, f) => Duration.Days(t - f) + 1,
S = Table.CombineColumns(
Table.Group(
Table.ExpandListColumn(
Table.CombineColumns(
Table.AddColumn(Source, "TD", each D([To Date], [From Date])),
{"From Date", "To Date"},
each List.Transform(
List.DateTimes(_{0}, D(_{1}, _{0}), Duration.From(1)),
each "Q" & Text.From(Date.QuarterOfYear(_)) & "-" & DateTime.ToText(_, "yy")
),
"Quarter"
),
"Quarter"
),
{"Store", "Quarter", "TD", "Amount"},
{{"D", each Table.RowCount(_)}}
),
{"TD", "Amount", "D"},
each _{2} / _{0} * _{1},
"Amount"
)
in
SPower Query solution 2 for Allocate Amounts by Quarter Days, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"From Date", type date}, {"To Date", type date}}),
Amt = Table.AddColumn(Type, "Amt", each [
a =[From Date],
b = [To Date],
c = Number.From( Duration.Days(b-a))+1 ,
d = List.Dates(a,c,hashtag#duration(1,0,0,0)),
e = [Amount]/c,
f = List.Distinct( List.Transform(d,Date.EndOfQuarter)),
g = List.RemoveLastN( List.Sort( List.Combine({{a},f,{b}})),1),
h = List.Numbers(1,List.Count(g)),
i = List.Transform(g, each Number.From(_) ),
j = List.Transform(h, each i{_}-i{_-1} ),
k = List.Transform( List.Combine({{ List.First(j)+1},List.RemoveLastN( List.RemoveFirstN(j))}),each Text.From( Number.Round( _ * e))),
l =List.Distinct( List.Transform(g, each "Q"&Text.From (Date.QuarterOfYear(_))&"-"& Text.End( Text.From(Date.Year(_)),2))),
m = List.RemoveLastN( List.Transform(h, each l{_-1}&"%"&k{_-1}))][m]),
Keep = Table.SelectColumns(Amt,{"Store", "Amt"}),
Xpand = Table.ExpandListColumn(Keep, "Amt"),
Split = Table.SplitColumn(Xpand, "Amt", Splitter.SplitTextByDelimiter("%"), {"Quarter", "Amount"}),
ToNum = Table.TransformColumnTypes(Split,{{"Amount", Int64.Type}})
in
ToNum
Power Query solution 3 for Allocate Amounts by Quarter Days, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Record = Table.AddColumn (
Source,
"R",
each [
F = Date.From ( [From Date] ),
T = Date.From ( [To Date] ),
TD = Number.From ( T - F ) + 1,
G = List.Generate (
() => [ a = - 1, c = F ],
( f ) => f[c] < Date.EndOfQuarter ( T ) + hashtag#duration ( 1, 0, 0, 0 ),
( f ) => [
a = f[a] + 1,
b = Date.StartOfQuarter ( Date.AddQuarters ( F, a ) ),
c = Date.EndOfQuarter ( b ),
d = Number.From ( List.Min ( { T, c } ) - List.Max ( { F, b } ) ),
e = ( d + 1 ) * [Amount] / TD,
g = Date.QuarterOfYear ( b ),
h = Date.Year ( b )
],
( f ) => [
Store = [Store],
Qtr = "Qtr-" & Text.From ( f[g] ) & " " & Text.From ( f[h] ),
Amount = Int64.From ( f[e] )
]
),
R = List.Skip ( G )
][R]
),
Return = Table.FromRecords ( List.Union ( Record[R] ) )
in
Return
Power Query solution 4 for Allocate Amounts by Quarter Days, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
(y) =>
let
a = {Number.From(Date.From(y[From Date])) .. Number.From(Date.From(y[To Date]))},
b = List.Transform(a, Date.From),
c = List.Transform(
b,
each "Q" & Text.From(Date.QuarterOfYear(_)) & "-" & Text.End(Text.From(Date.Year(_)), 2)
),
d = List.Transform(
List.Distinct(c),
each {
y[Store],
_,
Number.Round(List.Count(List.Select(c, (x) => x = _)) * y[Amount] / List.Count(b))
}
),
e = Table.FromRows(d, {"Store", "Quarter", "Amount"})
in
e
)[A]
)
in
SolPower Query solution 5 for Allocate Amounts by Quarter Days, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.TransformColumnTypes(
Source,
{{"Store", type text}, {"From Date", type date}, {"To Date", type date}, {"Amount", Int64.Type}}
),
B = Table.AddColumn(A, "Dl", each {Number.From([From Date]) .. Number.From([To Date])}),
C = Table.AddColumn(B, "APD", each [Amount] / List.Count([Dl])),
D = Table.SelectColumns(C, {"Store", "Dl", "APD"}),
E = Table.ExpandListColumn(D, "Dl"),
F = Table.TransformColumnTypes(E, {{"Dl", type date}, {"APD", type number}}),
G = Table.AddColumn(F, "Quarter", each Date.QuarterOfYear([Dl]), Int64.Type),
H = Table.TransformColumns(G, {{"Quarter", each "Q" & Text.From(_, "en-US"), type text}}),
I = Table.AddColumn(H, "YY", each Text.End(Text.From([Dl]), 2)),
J = Table.CombineColumns(
I,
{"Quarter", "YY"},
Combiner.CombineTextByDelimiter("-", QuoteStyle.None),
"Quarter.1"
),
K = Table.Group(
J,
{"Store", "Quarter.1"},
{{"Amount", each Number.Round(List.Sum([APD]), 0), type nullable number}}
)
in
KPower Query solution 6 for Allocate Amounts by Quarter Days, proposed by Venkata Rajesh:
let
Source = Data,
Dates = Table.AddColumn(
Source,
"Dates",
each List.Transform({Number.From([From Date]) .. Number.From([To Date])}, each Date.From(_))
),
Expand = Table.ExpandListColumn(Dates, "Dates"),
Quarter = Table.AddColumn(
Expand,
"Qtr",
each "Q"
& Text.From(Date.QuarterOfYear([Dates]))
& "-"
& Text.End(Text.From(Date.Year([Dates])), 2)
),
Group = Table.Group(
Quarter,
{"Store", "Qtr"},
{
{
"Amount",
each [
store = List.Min([Store]),
days = Table.RowCount(_),
sdays = Table.RowCount(Table.SelectRows(Quarter, each [Store] = store)),
Amt = Number.Round((days / sdays) * List.Min([Amount]))
][Amt],
Int64.Type
}
}
)
in
GroupPower Query solution 7 for Allocate Amounts by Quarter Days, proposed by Szabolcs Phraner:
let
Source = ...,
QuarterFormatting = (D as date) =>
let
Q = "Q" & Text.From(Date.QuarterOfYear(D)),
Y = Date.ToText(D, [Format = "yy"])
in
Q & "-" & Y,
Generate_AmountsForPeiod = Table.TransformRows(
Source,
(Row) =>
List.Generate(
() => Row[From Date],
each _ <= Row[To Date],
each Date.AddDays(_, 1),
each [
Store = Row[Store],
Quarter = QuarterFormatting(_),
Amount = Row[Amount] / Duration.Days(Row[To Date] - Row[From Date])
]
)
),
TableFromRec = Table.FromRecords(
List.Combine(Generate_AmountsForPeiod),
type table [Store = text, Quarter = text, Amount = number]
),
GroupBy = Table.Group(
TableFromRec,
{"Store", "Quarter"},
{{"Amount", each List.Sum([Amount]), type number}}
),
Rounding = Table.TransformColumns(GroupBy, {{"Amount", each Number.Round(_, 0), type number}})
in
RoundingSolving the challenge of Allocate Amounts by Quarter Days with Excel
Excel solution 1 for Allocate Amounts by Quarter Days, proposed by Bo Rydobon 🇹🇭:
=REDUCE(F1:H1,
D2:D4,
LAMBDA(a,
v,
LET(b,
@+v:B4,
c,
@+v:C4,
p,
EOMONTH(
b,
FLOOR(
MONTH(
b
)-1,
3
)-MONTH(
b
)
),
q,
EOMONTH(
c,
CEILING(
MONTH(
c
),
3
)-MONTH(
c
)
),
r,
EOMONTH(
p,
SEQUENCE(
YEARFRAC(
p,
q
)*4
)*3
),
VSTACK(a,
CHOOSE({1,
2,
3},
@+v:A4,
"Q"&MONTH(
r
)/3&-RIGHT(
YEAR(
r
),
2
),
ROUND(MAP(
r,
LAMBDA(
x,
MIN(
x,
c
)-MAX(
EOMONTH(
x,
-3
),
b-1
)
)
)*v/(c-b+1),
))))))Excel solution 2 for Allocate Amounts by Quarter Days, proposed by محمد حلمي:
=REDUCE(F1:H1,
B2:B4,
LAMBDA(a,
d,
LET(
e,
TAKE(
d:C4,
1,
-1
),
i,
@+d:A4,
r,
e-d+1,
j,
SEQUENCE(
r,
,
d
),
v,
"Q"&INT((MONTH(
j
)-1)/3)+1&-RIGHT(
YEAR(
j
),
2
),
u,
UNIQUE(
v
),
VSTACK(a,
IFNA(HSTACK(i,
u,
ROUND(MAP(u,
LAMBDA(c,
SUM((c=v)*TAKE(
d:D4,
1,
-1
)/r))),
)),
i)))))Excel solution 3 for Allocate Amounts by Quarter Days, proposed by محمد حلمي:
=REDUCE(F1:H1,
B2:B4,
LAMBDA(a,
d,
LET(
e,
TAKE(
d:C4,
1,
-1
),
i,
@+d:A4,
r,
e-d+1,
j,
SEQUENCE(
r,
,
d
),
v,
"Q"&INT((MONTH(
j
)-1)/3)+1
&-RIGHT(
YEAR(
j
),
2
),
u,
UNIQUE(
v
),
VSTACK(
a,
IFNA(
HSTACK(
i,
u,
ROUND(
MMULT(
XMATCH(
u,
v,
,
{1,
-1}
)*{-1,
1}+{0,
1},
{1;1}
)*TAKE(
d:D4,
1,
-1
)/r,
)
),
i
)
))))Excel solution 4 for Allocate Amounts by Quarter Days, proposed by 🇰🇷 Taeyong Shin:
=REDUCE(
F1:H1,
D2:D4,
LAMBDA(
a,
v,
LET(
s,
@+B4:v,
e,
@+C4:v,
d,
VSTACK(
EOMONTH(
s,
SEQUENCE(
CEILING(
YEARFRAC(
s,
e,
1
)*12,
1
),
,
0
)
),
e
),
r,
"Q"&MONTH(
MONTH(
d
)*10
)&-TEXT(
d,
"yy"
),
VSTACK(
a,
DROP(
GROUPBY(
CHOOSE(
{1,
2,
3},
XMATCH(
r,
r
),
@+A4:v,
r
),
DROP(
VSTACK(
@d-s+1,
DROP(
d,
1
)-d
),
-1
),
LAMBDA(
a,
b,
ROUND(
v*PERCENTOF(
a,
b
),
)
)
),
-1,
1
)
)
)
)
)Excel solution 5 for Allocate Amounts by Quarter Days, proposed by Julian Poeltl:
=VSTACK(
HSTACK(
"Store",
"Quarter",
"Amount"
),
LET(
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
BYROW(
A2:D4,
LAMBDA(
T,
LET(
A,
TAKE(
T,
,
1
),
B,
INDEX(
T,
,
2
),
C,
INDEX(
T,
,
3
),
D,
TAKE(
T,
,
-1
),
SQ,
SEQUENCE(
C-B+1,
,
B
),
Q,
MAP(
SQ,
LAMBDA(
E,
MONTH(
10*MONTH(
E
)
)
)
),
Y,
YEAR(
SQ
),
QQ,
A&","&"Q"&Q&"-"&RIGHT(
Y,
2
),
UQ,
UNIQUE(
QQ
),
CC,
MAP(
UQ,
LAMBDA(
A,
COUNTA(
FILTER(
QQ,
QQ=A
)
)
)
),
Am,
CC/SUM(
CC
)*D,
TEXTJOIN(
",",
,
UQ&","&Am
)
)
)
)
),
& ","
),
3
),
IFERROR(
R*1,
R
)
)
)Excel solution 6 for Allocate Amounts by Quarter Days, proposed by Duy Tùng:
=REDUCE(F1:H1,
D2:D4,
LAMBDA(x,
y,
LET(a,
@+A4:y,
b,
@+B4:y,
c,
@+C4:y,
g,
SEQUENCE(
c-b+1,
,
b
),
h,
"Q"&INT((MONTH(
g
)-1)/3)+1&TEXT(
g,
"-yy"
),
VSTACK(x,
IFNA(HSTACK(a,
DROP(GROUPBY(HSTACK(
YEAR(
g
),
h
),
h,
LAMBDA(u,
ROUND(ROWS(
u
)*y/(c-b+1),
0)),
,
0),
,
1)),
a)))))Excel solution 7 for Allocate Amounts by Quarter Days, proposed by Sunny Baggu:
=REDUCE(
HSTACK(
A1,
"Quarter",
D1
),
SEQUENCE(
ROWS(
A2:D4
)
),
LAMBDA(x,
y,
VSTACK(
x,
LET(
_e1,
LAMBDA(
k,
INDEX(
A2:D4,
y,
k
)
),
_c1,
_e1(1),
_c2,
_e1(2),
_c3,
_e1(3),
_c4,
_e1(4),
_a,
SEQUENCE(
_c3 - _c2 + 1,
,
_c2
),
_q,
ROUNDUP(
MONTH(
_a
) / 3,
0
),
_y,
RIGHT(
YEAR(
_a
),
2
),
_uy,
UNIQUE(
_q & "-" & _y
),
_v,
MAP(
_uy,
LAMBDA(
e,
MROUND(
_c4 * ROWS(
FILTER(
_q,
_q & "-" & _y = e
)
) / ROWS(
_q
),
1
)
)
),
IFNA(
HSTACK(
_c1,
"Q" & _uy,
_v
),
_c1
)
)
)
)
)Excel solution 8 for Allocate Amounts by Quarter Days, proposed by LEONARD OCHEA 🇷🇴:
=REDUCE(F1:H1,
A2:A4,
LAMBDA(a,
b,
LET(F,
OFFSET,
m,
F(
b,
,
1
),
n,
F(
b,
,
2
),
p,
F(
b,
,
3
),
d,
n-m+1,
s,
SEQUENCE(
d,
,
m
),
c,
p/d/s^0,
q,
"Q"&INT((MONTH(
s
)-1)/3)+1&"-"&RIGHT(
YEAR(
s
),
2
),
u,
UNIQUE(
q
),
z,
ROUND(
MMULT(
N(
TOROW(
q
)=u
),
c
),
0
),
VSTACK(
a,
HSTACK(
IF(
z,
b
),
u,
z
)
))))
with INDEX ( without volatil OFFSET )
=LET(t,
A2:D4,
REDUCE(F1:H1,
SEQUENCE(
ROWS(
t
)
),
LAMBDA(a,
b,
LET(I,
LAMBDA(
x,
INDEX(
t,
b,
x
)
),
m,
I(
2
),
d,
I(
3
)-m+1,
s,
SEQUENCE(
d,
,
m
),
c,
I(
4
)/d/s^0,
q,
"Q"&INT((MONTH(
s
)-1)/3)+1&"-"&RIGHT(
YEAR(
s
),
2
),
u,
UNIQUE(
q
),
z,
ROUND(
MMULT(
N(
TOROW(
q
)=u
),
c
),
0
),
VSTACK(
a,
HSTACK(
IF(
z,
I(
1
)
),
u,
z
)
)))))Excel solution 9 for Allocate Amounts by Quarter Days, proposed by Abdallah Ally:
=LET(
a,
A2:A4,
b,
REDUCE(
F1:H1,
a,
LAMBDA(
x,
y,
LET(
s,
LAMBDA(
m,
n,
OFFSET(
m,
,
n
)
),
t,
SEQUENCE(
s(
y,
2
)-s(
y,
1
)+1,
,
s(
y,
1
)
),
u,
"Q"&ROUNDUP(
MONTH(
t
)/3,
0
)&TEXT(
t,
"-yy"
),
VSTACK(
x,
REDUCE(
"",
UNIQUE(
u
),
LAMBDA(
p,
q,
VSTACK(
p,
HSTACK(
y,
q,
ROUND(
s(
y,
3
)*COUNTA(
FILTER(
u,
u=q
)
)/COUNTA(
u
),
0
)
)
)
)
)
)
)
)
),
FILTER(
b,
TAKE(
b,
,
1
)<>""
)
)Excel solution 10 for Allocate Amounts by Quarter Days, proposed by 🇵🇪 Ned Navarrete C.:
=REDUCE(F1:H1,
SEQUENCE(
ROWS(
A2:D4
)
),
LAMBDA(c,
v,
LET(e,
LAMBDA(
i,
INDEX(
A2:D4,
v,
i
)
),
f,
SEQUENCE(
e(
3
)-e(
2
)+1,
,
e(
2
)
),
q,
"Q"&ROUNDUP(
MONTH(
f
)/3,
)&"-"&TEXT(
f,
"yy"
),
u,
UNIQUE(
q
),
d,
MAP(
u,
LAMBDA(
x,
ROWS(
FILTER(
f,
q=x
)
)
)
),
a,
ROUND(e(
4
)*d/(e(
3
)-e(
2
)+1),
),
VSTACK(
c,
IFNA(
HSTACK(
e(
1
),
u,
a
),
e(
1
)
)
))))Excel solution 11 for Allocate Amounts by Quarter Days, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=HSTACK(DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
A2:A4,
B2:B4,
C2:C4,
LAMBDA(
x,
y,
z,
TEXTJOIN(
",",
,
LET(
e,
SEQUENCE(
12,
,
1,
1
),
c,
SEQUENCE(
,
z-y+1,
y,
1
),
a,
MAP(
MONTH(
c
),
c,
LAMBDA(
x,
y,
XLOOKUP(
x,
e,
"Q"&ROUNDUP(
e/3,
0
)
)&"-"&YEAR(
y
)
)
),
IF(
UNIQUE(
TOCOL(
a
)
)<>"",
x,
""
)
)
)
)
)&","
),
,
","
),
-1
),
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
B2:B4,
C2:C4,
LAMBDA(
m,
n,
TEXTJOIN(
",",
,
LET(
e,
SEQUENCE(
12,
,
1,
1
),
c,
SEQUENCE(
,
n-m+1,
m,
1
),
a,
MAP(
MONTH(
c
),
c,
LAMBDA(
x,
y,
XLOOKUP(
x,
e,
"Q"&ROUNDUP(
e/3,
0
)
)&"-"&YEAR(
y
)
)
),
HSTACK(
UNIQUE(
TOCOL(
a
)
)
)
)
)
)
)&","
),
,
","
),
-1
),
DROP(TEXTSPLIT(TEXTJOIN(,
,
MAP(B2:B4,
C2:C4,
D2:D4,
LAMBDA(k,
l,
h,
TEXTJOIN(",",
,
LET(e,
SEQUENCE(
12,
,
1,
1
),
c,
SEQUENCE(
,
l-k+1,
k,
1
),
a,
MAP(
MONTH(
c
),
c,
LAMBDA(
x,
y,
XLOOKUP(
x,
e,
"Q"&ROUNDUP(
e/3,
0
)
)&"-"&YEAR(
y
)
)
),
MAP(UNIQUE(
TOCOL(
a
)
),
LAMBDA(b,
ROUND(SUM(FILTER(BYCOL(c,
LAMBDA(d,
IF(AND(
d>=k,
d<=l
),
h/(l-k+1),
0))),
b=a)),
0)))))))&","),
,
","),
-1))Excel solution 12 for Allocate Amounts by Quarter Days, proposed by Andres Rojas Moncada:
=REDUCE({"Store",
"Quarter",
"Amount"},
D2:D4,
LAMBDA(a,
v,
LET(ti,
@+(A4:v),
fi,
@+(B4:v),
ff,
@+(C4:v),
dif,
ff-fi+1,
d,
RIGHT,
fe,
SEQUENCE(
dif,
,
fi
),
rg,
ti&"Q"&INT((MONTH(
fe
)-1)/3)+1&"-"&d(
YEAR(
fe
),
2
),
ag,
GROUPBY(
rg,
rg,
ROWS,
0,
0
),
ct,
TAKE(
ag,
,
1
),
di,
TAKE(
ag,
,
-1
),
VSTACK(
a,
SORTBY(
HSTACK(
LEFT(
ct
),
d(
ct,
5
),
di*v/dif
),
d(
ct,
2
)
)
))))Solving the challenge of Allocate Amounts by Quarter Days with Python
Python solution 1 for Allocate Amounts by Quarter Days, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("PQ_Challenge_188.xlsx", usecols="A:D", nrows=3)
test = pd.read_excel("PQ_Challenge_188.xlsx", usecols="F:H", nrows=11)
test.columns = test.columns.str.replace(".1", "")
result = input.assign(date=input.apply(lambda row: pd.date_range(row['From Date'], row['To Date'], freq='D'), axis=1))
.explode('date')
.assign(days=lambda df: (df['To Date'] - df['From Date']).dt.days + 1)
.assign(daily=lambda df: df['Amount'] / df['days'])
.assign(quarter=lambda df: df['date'].dt.quarter)
.assign(year=lambda df: df['date'].dt.year.astype(str).str[2:4])
.assign(Quarter=lambda df: 'Q' + df['quarter'].astype(str) + '-' + df['year'])
.groupby(['Store', 'Quarter', 'quarter', 'year'])
.agg(Amount=('daily', 'sum'))
.round(0)
.astype("int64")
.sort_values(by=['Store','year', 'quarter'])
.reset_index(drop=False)
.drop(columns=['quarter', 'year'])
print(result.equals(test)) # True
Solving the challenge of Allocate Amounts by Quarter Days with Python in Excel
Python in Excel solution 1 for Allocate Amounts by Quarter Days, proposed by Alejandro Campos:
df = xl("A1:D4", headers=True)
df = pd.concat(
[pd.DataFrame({
'Store': [row['Store']] * len(dates := pd.date_range(
row['From Date'], row['To Date'])),
'Date': dates,
'Amount': [row['Amount']] * len(dates)
}) for _, row in df.iterrows()],
ignore_index=True
)
df['Quarter'] = 'Q' + df['Date'].dt.quarter.astype(
str) + '-' + df['Date'].dt.strftime('%y')
df['QrtC'] = df.groupby(['Store', 'Quarter'])['Store'].transform('size')
df['StrC'] = df.groupby('Store')['Store'].transform('size')
df['Amount'] = round(df['Amount'] * df['QrtC'] / df['StrC'])
df = df[['Store', 'Quarter', 'Amount']].drop_duplicates(ignore_index=True)
df
Python in Excel solution 2 for Allocate Amounts by Quarter Days, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_188.xlsx'
df = pd.read_excel(file_path, usecols='A:D', nrows=3)
# Perform data wrangling
dfs = []
for i in df.index:
date_range = pd.date_range(start=df.iat[i, 1], end=df.iat[i, 2])
l = len(date_range)
values = {'Store': [df.iat[i, 0]] * l,
'Date': date_range,
'ThisAmount': [df.iat[i, 3]] * l
}
dfs.append(pd.DataFrame(values))
df = pd.concat(dfs, ignore_index=True)
df['Quarter'] = df['Date'].map(lambda x: f"Q{x.quarter}-{x.strftime('%y')}")
df['QuarterCount'] = df.groupby(['Store', 'Quarter'])['Quarter'].transform('count')
df['StoreCount'] = df.groupby('Store')['Store'].transform('count')
df['Amount'] = df.apply(lambda x: round(x[2] * x[4] / x[5]), axis=1)
df = df.iloc[:, [0, 3, 6]].drop_duplicates(ignore_index=True)
df
Solving the challenge of Allocate Amounts by Quarter Days with R
R solution 1 for Allocate Amounts by Quarter Days, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_188.xlsx", range = "A1:D4")
test = read_excel("Power Query/PQ_Challenge_188.xlsx", range = "F1:H11")
result = input %>%
mutate(date = map2(`From Date`, `To Date`, seq, by = "day"),
days = map_int(date, length),
daily = Amount / days) %>%
unnest(date) %>%
mutate(quarter = quarter(date),
year = year(date) %>% as.character() %>% str_sub(3, 4),
Quarter = paste0("Q",quarter,"-",year)) %>%
summarise(Amount = sum(daily) %>% round(0), .by = c(Store, Quarter))
identical(result, test)
# [1] TRUE
&