Prepare the groups which lie between two blank Amount1s and give the average of numbers appearing in those groups (groups shaded in light red). The average of remaining numbers (i.e. which appear where Amount1 is blank) should be put at the end and it will be called Remaining. Averages are rounded figures.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 203
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Average Between Blank Groups with Power Query
Power Query solution 1 for Average Between Blank Groups, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Gr = Table.TransformColumns(
Table.FromColumns(
Table.ToColumns(Source)
& {
List.Accumulate(
{0 .. Table.RowCount(Source) - 1},
{},
(s, i) =>
s
& {
if Source[Amount1]{i} = null then
0
else if ({null} & Source[Amount1]){i} = null then
List.Max(s, 0) + 1
else
List.Last(s)
}
)
},
Table.ColumnNames(Source) & {"Group"}
),
{"Group", each if _ = 0 then "Remaining" else "Group" & Text.From(_)}
),
Ans = Table.Sort(
Table.Group(
Gr,
"Group",
{
"Avg Amount",
each List.Average(List.Select(List.Combine(Table.ToRows(_)), each _ is number))
}
),
"Group"
)
in
AnsPower Query solution 2 for Average Between Blank Groups, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
P = Table.RemoveColumns(
Table.Group(
Table.FromRows(
List.Accumulate(
Table.ToRows(Source),
{},
(b, n) =>
let
l = List.Last(b)
in
b
& {
n
& {
{null, (List.Last(List.Select(b, each _{3}? <> null)){3}? ?? 0) + 1, l{3}}{
List.PositionOf({n{0}, l{0}, null}, null)
}
}
}
),
Table.ColumnNames(Source) & {"G"}
),
{"G"},
{
{"Group", each ("Group" & Text.From(List.Max([G]))) ?? "Remaining"},
{
"Avg Amount",
each List.Average(
List.TransformMany(
Table.ToRows(_),
each List.Select(List.RemoveLastN(_), each _ is number),
(i, _) => _
)
)
}
}
),
"G"
),
S = Table.Skip(P, 1) & Table.FirstN(P, 1)
in
SPower Query solution 3 for Average Between Blank Groups, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(Source, "Amount1", {"T", Table.Skip}, 0, (x, y) => Number.From(y = null))[T],
Nulls = {Table.SelectRows(Source, each [Amount1] = null)},
Transform = List.Transform(
Group & Nulls,
each [
TC = Table.ToColumns(_),
C = List.Combine(TC),
N = List.Select(C, (f) => f is number),
R = Int64.From(List.Average(N))
][R]
),
Groups = List.RemoveLastN(List.RemoveNulls(Transform)),
Records = List.Transform(
{1 .. List.Count(Groups)},
each [Group = "Group " & Text.From(_), Average = Groups{_ - 1}]
)
& {[Group = "Remaining", Average = List.Last(Transform)]},
Return = Table.FromRecords(Records, type table [Group = text, Average = Int64.Type])
in
ReturnPower Query solution 4 for Average Between Blank Groups, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = List.RemoveNulls(
Table.Group(
Source,
{"Amount1"},
{
{
"All",
each
let
a = _,
b = Table.SelectRows(a, each [Amount1] <> null),
c = List.Combine(Table.ToColumns(b)),
d = Number.Round(List.Average(List.Select(c, each _ is number)))
in
d
}
},
0,
(a, b) => Number.From(b[Amount1] = null)
)[All]
),
Rem = Number.Round(
List.Average(
List.Select(
List.RemoveNulls(
List.Combine(Table.ToColumns(Table.SelectRows(Source, each [Amount1] = null)))
),
each _ is number
)
)
),
Data = Group & {Rem},
G = List.Transform({1 .. List.Count(Data) - 1}, each "Group" & Text.From(_)) & {"Remaining"},
Sol = Table.FromColumns({G, Data}, {"Group", "Avg Amount"})
in
SolPower Query solution 5 for Average Between Blank Groups, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, "Amount1", {{"Avg Amount", each
let
a = Table.SelectRows(_, each [Amount1] <> null),
b = List.TransformMany(Table.ToColumns(a), each List.RemoveNulls(_), (a,b)=> b ),
c = Number.RoundUp(List.Average(List.Select(b, each _ is number)))
in c
}},0,(a,b)=> Number.From(b =null))[[Avg Amount]],
res = [a =Table.SelectRows(gp, each ([Avg Amount] <> null)),
b = Table.AddIndexColumn(a,"Group",1,1),
c = Table.TransformColumns(b,{"Group", each "Group"& Text.From(_) }),
d = c & hashtag#table({"Group","Avg Amount"},{{"Remaining",Number.RoundDown(List.Average(List.Select(List.TransformMany(Table.ToColumns(Table.SelectRows(Fonte,each [Amount1] = null)),(x)=> List.RemoveNulls(x), (a,b)=> b ), (y)=> y is number)))}})]
[d][[Group],[Avg Amount]]
in
res
Power Query solution 6 for Average Between Blank Groups, proposed by Eric Laforce:
let
fxListAvgR = (l as list) => Number.Round(List.Average(l)),
Source = Excel.CurrentWorkbook(){[Name = "tData203"]}[Content],
Group = Table.Group(
Source,
"Amount1",
{{"All", each _}},
GroupKind.Local,
(x, y) => Number.From(y = null)
),
Accumulate = List.Accumulate(
Group[All],
[g = {}, r = {}],
(s, c) =>
let
_R = Table.ToRows(c),
_Rem = List.Select(_R{0}, each _ is number),
_GrpAvg = fxListAvgR(List.Select(List.Combine(List.Skip(_R)), each _ is number)),
_NewGrp =
if (_GrpAvg = null) then
{}
else
{{"Group" & Text.From(List.Count(s[g]) + 1), _GrpAvg}}
in
[g = s[g] & _NewGrp, r = s[r] & _Rem]
),
Result = Table.FromRows(
Accumulate[g] & {{"Remaining", fxListAvgR(Accumulate[r])}},
{"Group", "Avg Amount"}
)
in
ResultPower Query solution 7 for Average Between Blank Groups, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.AddColumn(S, "L", each List.Select(Record.ToList(_), each Value.Is(_, Number.Type))),
A = Table.AddIndexColumn(S1, "Index", 0, 1, Int64.Type),
B = Table.AddColumn(
A,
"G",
each
if [Amount1] = null then
"Remaining"
else if S1[Amount1]{[Index] - 1} = null then
[Index]
else
null
),
C = Table.FillDown(B, {"G"}),
D = Table.ExpandListColumn(C, "L"),
E = Table.Group(
D,
{"G"},
{{"Avg Amount", each Number.Round(List.Average([L]), 0), type nullable number}}
),
F = Table.Sort(E, {{"G", Order.Ascending}}),
G = Table.AddIndexColumn(F, "Index", 1, 1, Int64.Type),
H = Table.AddColumn(
G,
"Group",
each if [G] <> "Remaining" then "Group " & Text.From([Index]) else [G]
),
Sol = Table.SelectColumns(H, {"Group", "Avg Amount"})
in
SolPower Query solution 8 for Average Between Blank Groups, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.AddColumn(Source, "Custom", each if [Amount1] = null then "a" else "b"),
S2 = Table.AddIndexColumn(S1, "Index", 1, 1),
S3 = Table.AddColumn(S2, "Custom.1", each if [Amount1] = null then [Index] else null),
S4 = Table.FillDown(S3, {"Custom.1"}),
S5 = Table.Group(S4, {"Custom.1", "Custom"}, {{"Count", each _}}),
S6 = Table.AddColumn(S5, "Custom.2", each if [Custom] = "a" then "Remaining" else [Custom.1]),
S7 = Table.RemoveColumns(S6, {"Custom.1", "Custom"}),
S8 = Table.Group(
S7,
{"Custom.2"},
{
{
"Avg",
each List.Average(
List.Select(
List.Combine(
Table.ToRows(
Table.RemoveColumns(Table.Combine(_[Count]), {"Custom.1", "Custom", "Index"})
)
),
each not ((try Number.From(_) otherwise null) is null)
)
)
}
}
),
S9 = Table.TransformColumns(S8, {{"Avg", each Number.Round(_, 0), type number}}),
S10 = Table.AddIndexColumn(S9, "Index", 0, 1),
S11 = Table.TransformColumnTypes(S10, {{"Index", type text}}),
S12 = Table.AddColumn(
S11,
"Group",
each if [Custom.2] = "Remaining" then "Remaining" else Text.Insert("Group", 5, [Index])
),
S13 = Table.SelectColumns(S12, {"Group", "Avg"}),
S14 = Table.Sort(S13, {{"Group", Order.Ascending}})
in
S14Solving the challenge of Average Between Blank Groups with Excel
Excel solution 1 for Average Between Blank Groups, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A14=0,
b,
A2:C14,
g,
IF(
a-1,
SCAN(
0,
a,
SUM
)
),
u,
SORT(
UNIQUE(
g
)
),
HSTACK(
IF(
u,
"Group"&SEQUENCE(
ROWS(
u
)
),
"Remaining"
),
MAP(
u,
LAMBDA(
h,
ROUND(
AVERAGE(
FILTER(
b,
g=h
)
),
)
)
)
)
)Excel solution 2 for Average Between Blank Groups, proposed by Rick Rothstein:
=LET(r,
A2:A14,
z,
B2:C14,
b,
BYROW(
IFERROR(
0+TEXTSPLIT(
TRIM(
CONCAT(
REDUCE(
"",
r,
LAMBDA(
a,
x,
VSTACK(
a,
IF(
x>0,
TEXTJOIN(
"|",
,
OFFSET(
x,
,
,
,
3
)&"|"
),
" "
)
)
)
)
)
),
"|",
" "
),
""
),
LAMBDA(
r,
ROUND(
AVERAGE(
r
),
)
)
),
v,
VSTACK(b,
ROUND(AVERAGE(IF((r=0)*(z>0),
z)),
)),
VSTACK(
{"Group",
"Avg Amount"},
HSTACK(
VSTACK(
"Group"&SEQUENCE(
COUNT(
v
)-1
),
"Remaining"
),
v
)
))Excel solution 3 for Average Between Blank Groups, proposed by Rick Rothstein:
=LET(z,
A2:C14,
n,
100000,
v,
VSTACK(TOCOL(ROUND(LET(s,
SCAN(
0,
BYROW(
z,
LAMBDA(
r,
IF(
INDEX(
r,
1
),
n*COUNT(
r
)+SUM(
r
)
)
)
),
LAMBDA(
a,
x,
IF(
x,
a+x,
0
)
)
),
REDUCE("",
SEQUENCE(
COUNT(
s
)
),
LAMBDA(a,
x,
VSTACK(a,
IF((x>1)*(INDEX(
s,
x
)=0),
MOD(
INDEX(
s,
x-1
),
n
)/INT(
INDEX(
s,
x-1
)/n
),
1/0))))),
0),
3),
ROUND(
AVERAGE(
FILTER(
z,
TAKE(
z,
,
1
)=""
)
),
)),
VSTACK(
{"Group",
"Avg Amount"},
HSTACK(
VSTACK(
"Group"&SEQUENCE(
COUNT(
v
)-1
),
"Remaining"
),
v
)
))Excel solution 4 for Average Between Blank Groups, proposed by محمد حلمي:
=LET(x,
A2:A13,
b,
B2:C14,
j,
SCAN(0,
(x=0)*(A3:A14>0),
LAMBDA(
a,
v,
a+v
)),
u,
UNIQUE(
j
),
VSTACK(HSTACK("Group"&u,
MAP(u,
LAMBDA(v,
ROUND(AVERAGE(
IF((j=v)*(x>0),
A2:C13)),
)))),
HSTACK("Remining",
ROUND(AVERAGE(TOCOL(b/(b>0)/(A2:A14=0),
2)),
))))Excel solution 5 for Average Between Blank Groups, proposed by Kris Jaganah:
=LET(a,
A2:C14,
b,
TOCOL(
IF(
a=0,
"",
a
)
),
c,
SEQUENCE(
ROWS(
b
)
),
d,
TOCOL(IF((MOD(
c,
3
)=1)*(b=""),
{0,
1,
2}+c,
h),
3),
e,
IF(
ISNA(
XLOOKUP(
c,
d,
d
)
),
1,
0
),
f,
VSTACK(
1,
DROP(
e,
-1
)
),
g,
((e+f)*e)/1000,
h,
SCAN(
1,
g,
LAMBDA(
x,
y,
IF(
y=0,
ROUNDUP(
x,
0
),
y+x
)
)
),
i,
IF(
INT(
h
)=h,
"Remaining",
"Group"&INT(
h
)
),
j,
GROUPBY(
i,
b,
AVERAGE,
0,
0
),
IF(
ISNUMBER(
j
),
ROUND(
j,
0
),
j
))Excel solution 6 for Average Between Blank Groups, proposed by Julian Poeltl:
=LET(A,
A2:C14,
B,
BYROW(
A,
LAMBDA(
A,
SUM(
A
)
)
),
G,
IF(
TAKE(
A,
,
1
)<>"",
1,
0
),
Z,
IFERROR(
G*A,
0
),
F,
SCAN(,
IFERROR(IF((G=0)*(DROP(
G,
1
)=1),
1,
0),
0),
LAMBDA(
A,
B,
A+B
)),
AV,
MAP(
SEQUENCE(
3
),
LAMBDA&(
A,
LET(
T,
TOCOL(
FILTER(
Z,
F=A
)
),
AVERAGE(
FILTER(
T,
T>0
)
)
)
)
),
R,
FILTER(
A,
G=0
),
TR,
TOCOL(
R
),
VSTACK(
HSTACK(
"Group",
"Avg Amount"
),
HSTACK(
VSTACK(
"Group"&SEQUENCE(
3
),
"Remaining"
),
VSTACK(
AV,
AVERAGE(
FILTER(
TR,
TR>0
)
)
)
)
))Excel solution 7 for Average Between Blank Groups, proposed by Oscar Mendez Roca Farell:
=LET(
a,
A2:A14,
r,
ROW(
a
),
d,
A2:C14,
q,
FREQUENCY(
IF(
a,
r
),
IF(
a="",
r
)
),
f,
FILTER(
q,
q
),
P,
LAMBDA(
j,
ROUND(
AVERAGE(
j
),
)
),
HSTACK(
VSTACK(
"Group"&SEQUENCE(
ROWS(
f
)
),
"Remaining"
),
VSTACK(
MAP(
f,
SCAN(
,
f,
LAMBDA(
i,
x,
i+x
)
),
LAMBDA(
i,
j,
P(
TAKE(
TAKE(
FILTER(
d,
a
),
j
),
-i
)
)
)
),
P(
FILTER(
d,
a=""
)
)
)
)
)Excel solution 8 for Average Between Blank Groups, proposed by Sunny Baggu:
=LET(
t,
A2:C14,
n,
SEQUENCE(
ROWS(
t
)
),
c,
TAKE(
t,
,
1
) = "",
a,
n * c,
s,
DROP(
TOCOL(IFNA(IF((a <> 0) * (DROP(
a,
1
) = 0),
a,
x),
n),
3) + 1,
-1
),
b,
n * (1 - c),
e,
TOCOL(IF((b <> 0) * (DROP(
b,
1
) = 0),
n,
x),
3),
ga,
MAP(
s,
e,
LAMBDA(
k,
l,
ROUND(
AVERAGE(
TOCOL(
DROP(
--TAKE(
t,
l
),
k - 1
),
3
)
),
0
)
)
),
r,
ROUND(
AVERAGE(
TOCOL(
--FILTER(
IF(
t = "",
x,
t
),
c
),
3
)
),
0
),
VSTACK(
HSTACK(
"Group" & SEQUENCE(
ROWS(
ga
)
),
ga
),
HSTACK(
"Remaining",
r
)
)
)Excel solution 9 for Average Between Blank Groups, proposed by Sunny Baggu:
=LET(
_n,
SEQUENCE(
ROWS(
A2:A14
)
),
_b,
A2:A14 = "",
_f1,
FILTER(
_n,
1 - _b
),
_d1,
VSTACK(
DROP(
_f1,
1
) - DROP(
_f1,
-1
),
2
),
_e,
FILTER(
_f1,
_d1 <> 1
),
_f2,
FILTER(
_n,
_b
),
_d2,
DROP(
_f2,
1
) - DROP(
_f2,
-1
),
_s,
FILTER(
DROP(
_f2,
-1
),
_d2 <> 1
) + 1,
_c,
LAMBDA(
arr,
ROUND(
AVERAGE(
TOCOL(
IF(
--arr,
arr,
x
),
3
)
),
0
)
),
_a1,
MAP(
_s,
_e,
LAMBDA(
x,
y,
_c(
DROP(
TAKE(
A2:C14,
y
),
x - 1
)
)
)
),
_r,
_c(
FILTER(
B2:C14,
_b
)
),
VSTACK(
HSTACK(
"Group" & SEQUENCE(
ROWS(
_a1
)
),
_a1
),
HSTACK(
"Remaining",
_r
)
)
)Excel solution 10 for Average Between Blank Groups, proposed by Md. Zohurul Islam:
=LET(z,
A2:C14,
u,
TAKE(
z,
,
1
),
a,
ABS(
u=0
),
b,
MAP(
u,
LAMBDA(
x,
ABS(
x=OFFSET(
x,
-1,
0
)
)
)
),
c,
SCAN(
0,
a-b,
SUM
),
d,
UNIQUE(
c
),
e,
MAP(d,
LAMBDA(x,
ROUND(AVERAGE(FILTER(z,
(c=x)*(u<>""))),
0))),
f,
ISERROR(
e
),
g,
ROUND(
AVERAGE(
FILTER(
z,
u=""
)
),
0
),
h,
IF(
f,
g,
e
),
j,
IF(
f,
"Remaining",
"Group"&SEQUENCE(
ROWS(
e
)
)
),
k,
VSTACK(
{"Group",
"Avg Amount"},
HSTACK(
j,
h
)
),
k)Excel solution 11 for Average Between Blank Groups, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
j,
LET(
d,
LET(
b,
UNIQUE(
IF(
IF(
A2:A14="",
1,
""
)="",
SCAN(
0,
IF(
A2:A14="",
1,
0
),
LAMBDA(
a,
b,
SUM(
a,
b
)
)
),
""
)
),
FILTER(
b,
b<>""
)
),
VSTACK(
LET(
z,
UNIQUE(
LET(
y,
IFERROR(
MAP(
IF(
IF(
A2:A14="",
1,
0
)=0,
SCAN(
0,
IF(
A2:A14="",
1,
0
),
LAMBDA(
a,
b,
SUM(
a,
b
)
)
),
""
),
LAMBDA(
x,
XLOOKUP(
x,
d,
SEQUENCE(
COUNTA(
d
)
)
)
)
),
""
),
IF(
y="",
"",
"Group"&y
)
)
),
FILTER(
z,
z<>""
)
),
"Remaining"
)
),
HSTACK(
j,
MAP(
j,
LAMBDA(
i,
AVERAGE(
FILTER(
A2:C14,
i=LET(
c,
IF(
IF(
A2:A14="",
1,
""
)="",
SCAN(
0,
IF(
A2:A14="",
1,
0
),
LAMBDA(
a,
b,
SUM(
a,
b
)
)
),
""
),
LET(
y,
IFERROR(
MAP(
c,
LAMBDA(
x,
XLOOKUP(
x,
LET(
b,
UNIQUE(
c
),
FILTER(
b,
b<>""
)
),
SEQUENCE(
COUNTA(
LET(
b,
UNIQUE(
c
),
FILTER(
b,
b<>""
)
)
)
)
)
)
),
""
),
IF(
y="",
"Remaining",
"Group"&y
)
)
)
)
)
)
)
)
)Excel solution 12 for Average Between Blank Groups, proposed by Edwin Tisnado:
=LET(a,
A2:A14=0,
j,
(1-a)*SCAN(
0,
a,
LAMBDA(
x,
y,
x+y
)
),
u,
UNIQUE(
j
),
s,
SEQUENCE(
ROWS(
u
)
)-1,
k,
IF(
s,
"Group"&s,
"Remaining"
),
m,
DROP(REDUCE(0,
u,
LAMBDA(x,
y,
LET(r,
A2:C14*(j=y),
VSTACK(
x,
ROUND(
AVERAGE(
TOCOL(
IF(
r,
r,
1/0
),
2
)
),
)
)))),
1),
VSTACK(
E1:F1,
SORT(
HSTACK(
k,
m
)
)
))Solving the challenge of Average Between Blank Groups with R
R solution 1 for Average Between Blank Groups, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_203.xlsx"
input = read_excel(path, range = "A1:C14")
test = read_excel(path, range = "E1:F5")
result = input %>%
mutate(Text = as.numeric(Text),
Group = consecutive_id(is.na(Amount1)) / 2 * !is.na(Amount1)) %>%
mutate(Group = ifelse(is.na(Amount1), "Remaining", paste0("Group", Group))) %>%
summarise(nmb = list(c(Amount1, Amount2, Text)), .by = Group) %>%
mutate(nmb = map(nmb, ~.x[!is.na(.x)])) %>%
mutate(avg = map_dbl(nmb, ~mean(.x, na.rm = TRUE)) %>% round()) %>%
arrange(Group) %>%
select(Group, `Avg Amount` = avg)
identical(result, test)
# [1] TRUE
R solution 2 for Average Between Blank Groups, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
df <- read_excel("PQ/PQ_Challenge_203.xlsx", range = cell_cols("A:C"))
df |>
mutate(Group = consecutive_id(is.na(Amount1))*(!is.na(Amount1)),
Text = suppressWarnings(readr::parse_number(Text))) |>
pivot_longer(-Group, names_to = NULL, values_to = "amounts", values_drop_na = TRUE) |>
summarise(Avg_amount = round(mean(amounts)), .by = Group) |>
group_split(.by = Group == 0, .keep = FALSE) |>
map_if(.p= ~ mean(.x[["Group"]]) != 0,
.f = ~.x |>
mutate(Group = paste("Group", row_number())),
.else = ~ .x |>
mutate(Group = "Remaining")) |>
map_dfr(~.x)
&&