This challenge is contributed by Ahmad Syawal Ramli Align the activities month-wise for each project.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 220
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Distribute Activities by Month with Power Query
_x000D_Power Query solution 1 for Distribute Activities by Month, proposed by Zoran Milokanović:
let
Source = Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
{{"Project", each _}, {"Activities", each _}},
Date.StartOfMonth
),
I = List.Generate(
() => List.Min(Source[Start]),
each _ <= List.Max(Source[Finish]),
each Date.AddMonths(_, 1)
),
S = Table.FromRows(
List.TransformMany(
List.Distinct(Source[Project]),
each List.Zip(
List.Transform(
I,
(d) =>
Table.SelectRows(Source, (r) => r[Project] = _ and r[Start] <= d and d <= r[Finish])[
Activities
]
)
),
(i, _) => {i} & _
),
{"Project"} & List.Transform(I, each DateTime.ToText(_, "MMM-yy", "en-US"))
)
in
SPower Query solution 2 for Distribute Activities by Month, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.Group(A, {"Project"}, {"All", each let
a = Table.TransformColumnTypes(_,{{"Start", type date}, {"Finish", type date}}) ,
b = Table.AddColumn(a, "Date", each List.Distinct( List.Transform( List.Dates([Start], Number.From( [Finish]-[Start])+1,hashtag#duration(1,0,0,0)) , each Date.ToText(_,[Format ="MMM-yy"])))),
c = Table.ExpandListColumn(b,"Date"),
d = Table.Group(c, {"Date"}, {"Act", each [Activities]}),
e = Table.FromColumns(d[Act] ,d[Date]) in e}),
C = List.Distinct( List.Combine( List.Transform( B[All] ,each Table.ColumnNames(_)))),
D = Table.ExpandTableColumn(B, "All", C)
in D
Power Query solution 3 for Distribute Activities by Month, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Meses = Table.AddColumn(
Origen,
"A",
each
let
a = {Number.From([Start]) .. Number.From([Finish])},
b = List.Transform(a, each Date.ToText(Date.From(_), "MMM-yyyy")),
c = List.Distinct(b)
in
c
)[[Project], [Activities], [A]],
Expand = Table.ExpandListColumn(Meses, "A"),
Group1 = Table.Group(
Expand,
{"Project", "A"},
{
{
"B",
each Table.DemoteHeaders(
Table.Pivot(Table.AddIndexColumn(_, "Idx"), List.Distinct([A]), "A", "Activities")
)[Column3]
}
}
),
Group2 = Table.Group(
Group1,
{"Project"},
{{"C", each Table.PromoteHeaders(Table.FromColumns([B]))}}
),
Sol = Table.ExpandTableColumn(Group2, "C", Table.ColumnNames(Table.Combine(Group2[C])))
in
SolPower Query solution 4 for Distribute Activities by Month, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"Personalizar",
each List.Distinct(
List.Transform(
{Number.From([Start]) .. Number.From([Finish])},
(x) => Text.Proper(Date.ToText(Date.From(x), "MMM-yy"))
)
)
),
exp = Table.ExpandListColumn(add, "Personalizar"),
gp = Table.Group(
exp,
{"Project"},
{
{
"tab",
each
let
a = Table.AddIndexColumn(_, "Ind", 1, 1),
b = Table.RemoveColumns(a, {"Start", "Finish"}),
c = Table.Pivot(b, List.Distinct(b[Personalizar]), "Personalizar", "Activities"),
d = Table.RemoveColumns(c, {"Project", "Ind"})
in
Table.FromColumns(
List.Transform(Table.ToColumns(d), List.RemoveNulls),
Table.ColumnNames(d)
)
}
}
),
res = Table.ExpandTableColumn(gp, "tab", Table.ColumnNames(gp[tab]{0}))
in
resPower Query solution 5 for Distribute Activities by Month, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Type = Table.TransformColumnTypes(Source, {{"Start", type date}, {"Finish", type date}}),
f = each Text.Start(Date.MonthName(_), 3) & "-" & Text.End(Text.From(Date.Year(_)), 2),
Transform1 = Table.AddColumn(
Type,
"Dates",
each [
a = List.Dates([Start], Duration.Days([Finish] - [Start]) + 1, Duration.From(1)),
b = List.Distinct(List.Transform(a, each f(_)))
][b]
),
Expand = Table.ExpandListColumn(Transform1, "Dates"),
Group = Table.Group(Expand, {"Project", "Dates"}, {{"Activities", each [Activities]}}),
Pivot = Table.Pivot(Group, List.Distinct(Group[Dates]), "Dates", "Activities"),
Transform2 = Table.TransformRows(
Pivot,
each [
a = Record.ToList(_),
b = List.Max(List.Transform(List.RemoveNulls(List.Skip(a)), each List.Count(_))),
c = {List.Repeat({a{0}}, b)} & List.Transform(List.Skip(a), each if _ = null then {} else _),
d = List.Zip(c)
][d]
),
Result = Table.FromRows(List.Combine(Transform2), Table.ColumnNames(Pivot))
in
ResultPower Query solution 6 for Distribute Activities by Month, proposed by Eric Laforce:
let
fxListMonthName = (start as datetime, finish as datetime) =>
let
_LD = List.Generate(
() => Date.StartOfMonth(start),
each _ <= finish,
each Date.AddMonths(_, 1)
)
in
List.Transform(_LD, each DateTime.ToText(_, "MMM-yy")),
Source = Excel.CurrentWorkbook(){[Name = "tData220"]}[Content],
Group = Table.Group(
Source,
"Project",
{
"G",
(t) =>
let
_T1 = Table.TransformRows(
t,
each [[Activities]] & [Months = fxListMonthName([Start], [Finish])]
),
_T2 = Table.ExpandListColumn(Table.FromRecords(_T1), "Months"),
_T3 = Table.Group(_T2, {"Months"}, {"G", each [Activities]}),
_T4 = Table.FromColumns(_T3[G], _T3[Months])
in
_T4
}
),
Expand = Table.ExpandTableColumn(
Group,
"G",
fxListMonthName(List.Min(Source[Start]), List.Max(Source[Finish]))
)
in
ExpandPower Query solution 7 for Distribute Activities by Month, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Date", each {Number.From([Start]) .. Number.From([Finish])}),
B = Table.SelectColumns(A, {"Project", "Activities", "Date"}),
C = Table.ExpandListColumn(B, "Date"),
D = Table.TransformColumnTypes(C, {{"Date", type date}}),
E = Table.AddColumn(D, "MY", each Date.ToText([Date], "MMM-yy")),
F = Table.Group(E, {"Project", "MY"}, {{"T", each _}}),
G = Table.AddColumn(
F,
"L",
each Table.AddIndexColumn(Table.Distinct([T], {"Activities"}), "i", 1, 1)
),
H = Table.SelectColumns(G, {"L"}),
I = Table.ExpandTableColumn(
H,
"L",
{"Project", "Activities", "Date", "MY", "i"},
{"Project", "Activities", "Date", "MY", "i"}
),
J = Table.SelectColumns(I, {"i", "Project", "Activities", "MY"}),
K = Table.Pivot(J, List.Distinct(J[MY]), "MY", "Activities"),
L = Table.Sort(K, {{"Project", Order.Ascending}, {"i", Order.Ascending}}),
M = Table.RemoveColumns(L, {"i"})
in
MPower Query solution 8 for Distribute Activities by Month, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each List.Generate(
() => [Start],
(x) => x <= Date.EndOfMonth([Finish]),
(x) => Date.AddMonths(x, 1),
(x) => Date.ToText(Date.From(x), "MMM-yy")
)
),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom", {"Start", "Finish"}),
#"Pivoted Column" = Table.Pivot(
#"Removed Columns",
List.Distinct(#"Removed Columns"[Custom]),
"Custom",
"Activities",
each _
),
#"Added Custom1" = Table.AddColumn(
#"Pivoted Column",
"Custom",
each Table.FromRows(List.Zip(List.Skip(Record.ToList(_))))
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1", {"Project", "Custom"}),
#"Expanded Custom1" = Table.ExpandTableColumn(
#"Removed Other Columns",
"Custom",
{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"},
List.Skip(Table.ColumnNames(#"Pivoted Column"))
)
in
#"Expanded Custom1"Power Query solution 9 for Distribute Activities by Month, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{
{"Project", type text},
{"Activities", type text},
{"Start", type datetime},
{"Finish", type datetime}
}
),
AddedCustom = Table.AddColumn(
ChangedType,
"M",
each List.Distinct(
List.Transform(
{Number.From([Start]) .. Number.From([Finish])},
each Date.ToText(Date.From(_), "MMM-yy")
)
)
),
RemovedOtherColumns = Table.SelectColumns(AddedCustom, {"Project", "Activities", "M"}),
ExpandedM = Table.ExpandListColumn(RemovedOtherColumns, "M"),
PivotedColumn = Table.Pivot(ExpandedM, List.Distinct(ExpandedM[M]), "M", "Activities", each _),
AddedCust = Table.AddColumn(
PivotedColumn,
"Custom",
each Table.FromColumns(
Record.ToList(Record.RemoveFields(_, {"Project"})),
List.Skip(Table.ColumnNames(PivotedColumn))
)
)[[Project], [Custom]],
ExpandedCustom = Table.ExpandTableColumn(
AddedCust,
"Custom",
List.Skip(Table.ColumnNames(PivotedColumn))
)
in
ExpandedCustomPower Query solution 10 for Distribute Activities by Month, proposed by Gertjan Davies:
let
Source = Problem,
Months = Table.AddColumn(
Source,
"Months",
each List.Transform(
List.Select(
{
(Date.Month([Start]) + 100 * Date.Year([Start])) .. (
Date.Month([Finish]) + 100 * Date.Year([Finish])
)
},
each (Number.Mod(_, 100) >= 1) and (Number.Mod(_, 100) <= 12)
),
each Date.ToText(Date.FromText(Text.From(_), [Format = "yyyyMM"]), [Format = "MMM-yy"])
)
),
Expand = Table.ExpandListColumn(Months, "Months"),
A_per_M = Table.Group(
Expand,
{"Project", "Months"},
{{"Activities", each [Activities], type list}}
),
Group_P = Table.Group(
A_per_M,
{"Project"},
{{"Details", each _, type table [Project = nullable text, Months = text, Activities = list]}}
),
Prep = Table.AddColumn(
Group_P,
"Prep",
each Table.FromColumns([Details][Activities], [Details][Months])
),
Relevant = Table.RemoveColumns(Prep, {"Details"}),
Result = Table.ExpandTableColumn(Relevant, "Prep", List.Distinct(Expand[Months]))
in
ResultSolving the challenge of Distribute Activities by Month with Excel
_x000D_Excel solution 1 for Distribute Activities by Month, proposed by Bo Rydobon 🇹🇭:
=LET(p,
A2:A9,
s,
EOMONTH(
+C2:C9,
-1
)+1,
f,
D2:D9,
m,
EDATE(
@s,
SEQUENCE(
,
YEARFRAC(
@s-1,
EOMONTH(
MAX(
f
),
0
)
)*12,
0
)
),
REDUCE(HSTACK(
A1,
TEXT(
m,
"mmm-y"
)
),
UNIQUE(
p
),
LAMBDA(a,
v,
VSTACK(a,
IFNA(REDUCE(v,
m,
LAMBDA(b,
n,
HSTACK(b,
FILTER(B2:B9,
(p=v)*(s<=n)*(n<=f),
"")))),
REPT(
v,
HSTACK(
0,
m
)=0
))))))Excel solution 2 for Distribute Activities by Month, proposed by Julian Poeltl:
=LET(PP,
A2:A9,
Ac,
B2:B9,
St,
C2:C9,
Fi,
D2:D9,
S,
HSTACK(1,
EOMONTH(EOMONTH(MIN(
St
),
--SEQUENCE(,
(MAX(
Fi
)-MIN(
St
))/29)),
-2)+1),
D,
DROP(REDUCE("",
UNIQUE(
PP
),
LAMBDA(X,
P,
VSTACK(X,
IFNA(REDUCE(IF(
ROWS(
X
)>1,
P,
VSTACK(
"Project",
P
)
),
S,
LAMBDA(A,
B,
HSTACK(A,
IF(ROWS(
X
)=1,
VSTACK(TEXT(
B,
"MMM-YY"
),
FILTER(Ac,
(St<=EOMONTH(
B,
0
))*(Fi>=B)*(PP=P),
"")),
FILTER(Ac,
(St<=EOMONTH(
B,
0
))*(Fi>=B)*(PP=P),
""))))),
"")))),
1),
HSTACK(
SCAN(
,
TAKE(
D,
,
1
),
LAMBDA(
A,
B,
IF(
B="",
A,
B
)
)
),
DROP(
D,
,
2
)
))Excel solution 3 for Distribute Activities by Month, proposed by Oscar Mendez Roca Farell:
=LET(
s,
C2:C9,
f,
D2:D9,
e,
EDATE(
@+s,
SEQUENCE(
,
DATEDIF(
@+s,
MAX(
f
),
"m"
)+1,
0
)
),
REDUCE(
HSTACK(
A1,
TEXT(
e,
"mmm-y"
)
),
UNIQUE(
A2:A9
),
LAMBDA(
j,
y,
VSTACK(
j,
IFNA(
HSTACK(
y,
DROP(
REDUCE(
"",
e,
LAMBDA(
i,
x,
LET(
m,
FILTER(
B2:D9,
A2:A9=y
),
IFNA(
HSTACK(
i,
FILTER(
TAKE(
m,
,
1
),
MAP(
INDEX(
m,
,
2
),
INDEX(
m,
,
3
),
LAMBDA(
& a,
b,
MONTH(
MEDIAN(
x,
a,
b
)
)=MONTH(
x
)
)
),
""
)
),
""
)
)
)
),
,
1
)
),
y
)
)
)
)
)Excel solution 4 for Distribute Activities by Month, proposed by LEONARD OCHEA 🇷🇴:
=LET(t,
A2:D9,
I,
INDEX,
H,
HSTACK,
s,
SEQUENCE,
m,
UNIQUE(
DROP(
REDUCE(
"",
s(
ROWS(
t
)
),
LAMBDA(
a,
b,
LET(
I,
LAMBDA(
x,
I(
t,
b,
x
)
),
n,
s(
I(
4
)-I(
3
)+1,
,
I(
3
)
),
VSTACK(
a,
H(
TEXT(
n,
"mmm-yy"
),
IF(
n,
H(
I(
1
),
I(
2
)
)
)
)
)
)
)
),
1
)
),
u,
I(
m,
,
1
)&I(
m,
,
2
),
f,
s(
ROWS(
u
)
),
o,
BYROW((u=TOROW(
u
))*(f>=TOROW(
f
)),
SUM),
p,
PIVOTBY(
I(
m,
,
2
)&o,
I(
m,
,
1
),
I(
m,
,
3
),
SINGLE,
,
0,
,
0
),
r,
DROP(
p,
,
1
),
H(LEFT(
I(
p,
,
1
)
),
SORTBY(r,
--(1&I(
r,
1,
)))))Excel solution 5 for Distribute Activities by Month, proposed by Nonbow Wu:
=LET(
da,
A2:D9,
pj,
TAKE(
da,
,
1
),
mm,
EOMONTH(
MIN(
da
),
SEQUENCE(
,
DATEDIF(
MIN(
da
),
MAX(
da
),
"m"
)+1
)-2
)+1,
foo,
LAMBDA(
p,
LET(
t,
REDUCE(
"",
mm,
LAMBDA(
a,
v,
HSTACK(
a,
IFERROR(
TOCOL(
BYROW(
FILTER(
da,
pj=p
),
LAMBDA(
r,
IFS(
MEDIAN(
EOMONTH(
INDEX(
r,
3
),
-1
)+1,
INDEX(
r,
4
),
v
)=v,
INDEX(
r,
2
)
)
)
),
2
),
""
)
)
)
),
IFNA(
HSTACK(
IF(
SEQUENCE(
ROWS(
t
)
),
p
),
DROP(
t,
,
1
)
),
""
)
)
),
REDUCE(
HSTACK(
A1,
TEXT(
mm,
"mmm-yy"
)
),
UNIQUE(
pj
),
LAMBDA(
A,
v,
VSTACK(
A,
foo(
v
)
)
)
)
)Solving the challenge of Distribute Activities by Month with Python
_x000D_Python solution 1 for Distribute Activities by Month, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_220.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:D", nrows=9)
test = pd.read_excel(path, sheet_name=0, usecols="A:I", skiprows=12, nrows=6).fillna("")
input[['Start', 'Finish']] = input[['Start', 'Finish']].apply(pd.to_datetime).apply(lambda x: x.dt.to_period('M').dt.to_timestamp())
input = input.assign(seq=input.apply(lambda x: pd.date_range(x['Start'], x['Finish'], freq='MS'), axis=1)).explode('seq').drop(columns=['Start', 'Finish'])
input['rn'] = input.groupby(['Project', 'seq']).cumcount() + 1
result = input.pivot_table(index=['Project', 'rn'], columns='seq', values='Activities', aggfunc=lambda x: ' '.join(x)).fillna('').reset_index()
result.columns.name = None
result = result.drop(columns='rn')
result.columns = test.columns
Solving the challenge of Distribute Activities by Month with Python in Excel
_x000D_Python in Excel solution 1 for Distribute Activities by Month, proposed by Alejandro Campos:
from datetime import datetime
df = xl("A1:D9", headers=True)
df['Dates'] = df.apply(lambda r: pd.date_range(r['Start'], r['Finish']).strftime('%b-%y'), axis=1)
df = (df.explode('Dates')
.drop_duplicates()
.groupby(['Project', 'Dates'], sort=False)['Activities']
.agg(', '.join)
.reset_index()
.pivot(index='Project', columns='Dates', values='Activities')
.reset_index().rename_axis(None, axis=1))
expand_rows = lambda row: pd.DataFrame(
[[row[0]] * max(len(str(x).split(', ')) if pd.notna(x) else 0 for x in row[1:])] +
[[y if pd.notna(y) else '' for y in str(x).split(', ') + [''] * (max(len(str(x).split(', ')) if pd.notna(x) else 0 for x in row[1:]) - len(str(x).split(', '))) if pd.notna(x)] for x in row[1:]]
).T
df_expanded = (pd.concat([expand_rows(row) for _, row in df.iterrows()], ignore_index=True)
.set_axis(df.columns, axis=1)
.reindex(columns=['Project'] + sorted(df.columns[1:], key=lambda x: datetime.strptime(x, '%b-%y'))))
df_expanded
Python in Excel solution 2 for Distribute Activities by Month, proposed by Abdallah Ally:
from datetime import datetime
df = xl("A1:D9", headers=True)
# Perform data manipulation
df['Dates'] = df.apply(lambda x: [y.strftime('%b-%y') for y in pd.date_range(x[2], x[3])], axis=1)
df = df.explode(column='Dates').drop_duplicates()
df = df.groupby(['Project', 'Dates'], sort=False)['Activities'].agg(', '.join).reset_index()
df = pd.pivot(data=df, index='Project', columns='Dates', values='Activities').reset_index()
df.columns.name = ''
values = []
for i in df.index:
a = df.iloc[i, :].values
b = max([len(x.split(', ')) if pd.notna(x) else 0 for x in a[1:]])
c = [[a[0]] * b] + [[''] * b if pd.isna(x) else x.split(', ') + [''] * (b - len(x.split(', '))) for x in a[1:]]
values.extend(zip(*c)) # Unpacking list items and zip
df = pd.DataFrame(values, columns=df.columns).fillna('')
df = df[['Project'] + sorted(df.columns[1:], key=lambda x: datetime.strptime(x, '%b-%y'))]
df
Solving the challenge of Distribute Activities by Month with R
_x000D_R solution 1 for Distribute Activities by Month, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_220.xlsx"
input = read_excel(path, range = "A1:D9")
test = read_excel(path, range = "A13:I18") %>% replace(is.na(.), "")
result = input %>%
mutate(Start = floor_date(Start, "month"),
Finish = floor_date(Finish, "month")) %>%
mutate(seq = map2(Start, Finish, seq, by = "month")) %>%
unnest(seq) %>%
select(-Start, -Finish) %>%
mutate(rn = row_number(), .by = c("Project", "seq")) %>%
pivot_wider(names_from = seq, values_from = Activities, values_fill = "") %>%
select(-rn)
names(result) = names(test)
result == test