List the fruits and the sum of total amount against a fruit. Also insert a Total row at the bottom after one blank row.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 259
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List the fruits and the with Power Query
Power Query solution 1 for List the fruits and the, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.FromRows(
List.Split(
List.Select(List.Combine(Table.ToColumns(A)), (x) => Text.Length(Text.From(x)) > 1),
2
),
{"Fruits", "Amt"}
),
C = Table.Group(B, "Fruits", {"Amount", each List.Sum([Amt])}),
D = Table.Sort(C, "Fruits"),
E = Table.InsertRows(
D,
Table.RowCount(D),
{[Fruits = null, Amount = null], [Fruits = "Total Amount", Amount = List.Sum(D[Amount])]}
)
in
EPower Query solution 2 for List the fruits and the, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl = Table.FromRows(
List.Combine(List.Transform(Table.Split(Source, 2), Table.ToColumns)),
{"Fruits", "Amount"}
),
Grp = Table.Group(
Table.SelectRows(Tbl, each [Amount] is number),
"Fruits",
{{"Amount", each List.Sum([Amount])}}
),
Sol = Grp
& Table.FromRows(
{{null, null}} & {{"Total Amount", List.Sum(Grp[Amount])}},
Table.ColumnNames(Grp)
)
in
SolPower Query solution 3 for List the fruits and the, proposed by Luan Rodrigues:
let
Fonte = let
a = List.Combine(Table.ToColumns(Tabela1)),
b = List.Select(a,(x)=> x <> null and Text.Length(Text.From(x)) <> 1 ),
c = Table.FromRows(List.Split(b,2),{"Fruits","Amount"})
in c,
grp = Table.Group(Fonte, {"Fruits"}, {"Amount", each List.Sum([Amount])}),
clss = Table.Sort(grp,{"Fruits",0}),
res = clss & hashtag#table(Table.ColumnNames(clss), {{null,null},{"Total Amount",List.Sum(clss[Amount]) }})
in
res
Power Query solution 4 for List the fruits and the, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 =
Table.ExpandTableColumn(
Table.FromList(
List.Transform(Table.Split(Source,2), (x)=> Table.SelectRows(Table.Transpose(x), each [Column2] is number)),
Splitter.SplitByNothing()),
"Column1", {"Column1", "Column2"}),
Result =
Table.SelectRows(
Table.Group(Custom1, {"Column1"}, {{"Amount", each List.Sum([Column2])}}), each [Amount] <> null)
& hashtag#table({"Column1", "Amount"}, {{"Total", List.Sum(Custom1[Column2])}})
in Result
Power Query solution 5 for List the fruits and the, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = List.Transform(
Table.Split(Source, 2),
each Table.FromRows(List.Zip(Table.ToRows(_)), {"Fruits", "Amount"})
),
Select = Table.SelectRows(Table.Combine(Transform), each Text.Length([Fruits]) > 1),
Group = Table.Group(
Select,
"Fruits",
{"Amount", each List.Sum([Amount])},
1,
(x, y) => Comparer.Ordinal(x, y)
),
Rows = {{null, null}, {"Total Amount", List.Sum(Group[Amount])}},
Result = Table.Combine({Group, Table.FromRows(Rows, {"Fruits", "Amount"})})
in
ResultPower Query solution 6 for List the fruits and the, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Split(List.Combine(Table.ToColumns(S)), 2),
b = Table.SelectRows(Table.FromRows(a), each [Column2] is number),
c = Table.Group(b, "Column1", {"Amount", each List.Sum([Column2])}),
d = Table.RenameColumns(Table.Sort(c, {"Column1", 0}), {"Column1", "Fruits"}),
e = d
& Table.FromRows(
{{"Total Amount"} & List.Transform(List.Skip(Table.ToColumns(d)), List.Sum)},
Table.ColumnNames(d)
),
Sol = Table.InsertRows(e, Table.RowCount(e) - 1, {[Fruits = "", Amount = ""]})
in
SolPower Query solution 7 for List the fruits and the, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData259"]}[Content],
Transform = List.Select(
List.Split(List.Combine(Table.ToColumns(Source)), 2),
each _{0} <> null and Value.Is(_{1}, type number)
),
Group = Table.Group(
Table.FromRows(Transform, {"Fruit", "V"}),
"Fruit",
{"Amount", each List.Sum([V])}
),
Result = Table.Sort(Group, "Fruit")
& Table.FromRows({{null, null}, {"Total", List.Sum(Group[Amount])}}, {"Fruit", "Amount"})
in
ResultPower Query solution 8 for List the fruits and the, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = [
A = List.Combine(Table.ToColumns(Source)),
B = List.Select(List.Split(A, 2), each _{1} is number)
][B],
Recs = List.Accumulate(
Rows,
[],
(a, v) => a & Record.AddField([], v{0}, (try Record.Field(a, v{0}) otherwise 0) + v{1})
),
Res = [
A = Record.ReorderFields(Recs, List.Sort(Record.FieldNames(Recs))),
B = {
Record.FieldNames(A) & {null, "Total Amount"},
Record.ToList(A) & {null, List.Sum(Record.ToList(A))}
},
C = Table.FromColumns(B, {"Fruits", "Amount"})
][C]
in
ResPower Query solution 9 for List the fruits and the, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = [
A = List.Combine(Table.ToColumns(Source)),
B = List.Select(List.Split(A, 2), each Text.Length(_{0}) > 1)
][B],
Res = [
A = Table.FromRows(Rows, {"Fruits", "Amount"}),
B = Table.Group(A, "Fruits", {"Amount", each List.Sum([Amount])}),
C = Table.Sort(B, {"Fruits", 0}),
D = hashtag#table(Table.ColumnNames(A), {{null, null}, {"Total Amount", List.Sum(A[Amount])}}),
E = C & D
][E]
in
Res
Power Query solution 10 for List the fruits and the, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Sort(Table.Group(Table.FromRows(List.Combine(List.Transform(Table.Split(Source,2), (f) => List.Select(Table.ToColumns(f), each _{1} is number))),{"Fruits","Amount"}),"Fruits", {{"Amt", each List.Sum([Amount])}}),"Fruits"),
Result = Group & hashtag#table({"Fruits","Amt"},{{null,null},{"Total Amount",List.Sum(Group[Amt])}})
in
Result
Power Query solution 11 for List the fruits and the, proposed by Peter Krkos:
let
L = List.Combine(Table.ToColumns(Source)),
Gen = List.Accumulate(
List.Positions(L),
{},
(s, c) =>
if (try Number.From(L{c}) otherwise false) is number then
s & {{L{c - 1}, Int64.From(L{c})}}
else
s
),
Tbl = Table.FromRows(
Gen & {{null, null}} & {{"Total Amount", List.Sum(List.Transform(Gen, each _{1}))}},
type table [Fruits = text, Amount = Int64.Type]
)
in
TblPower Query solution 12 for List the fruits and the, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToList = List.Combine(Table.ToColumns(Source)),
Clean = Table.SelectRows(
Table.FromColumns(
{List.Alternate(ToList, 1, 1, 1), List.Alternate(ToList, 1, 1)},
{"Fruits", "Amount"}
),
each (Value.Is([Amount], type number))
),
Group = Table.Group(Clean, {"Fruits"}, {{"Amount", each List.Sum([Amount]), type any}}),
Result = Table.Combine(
{
Table.Sort(Group, {{"Fruits", Order.Ascending}}),
Table.FromRecords(
{
[Fruits = null, Amount = null],
[Fruits = "Total Amount", Amount = List.Sum(Group[Amount])]
}
)
}
)
in
ResultPower Query solution 13 for List the fruits and the, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
P = {"Fruits", "Amount"},
L = let x = List.Combine(Table.ToColumns(H)), y = List.Zip({x, List.Skip(x)}) in Table.FromRows(List.Select(y, each _{1} is number), {P{0}, "y"}),
C = Table.Sort(Table.Group(L, P{0}, {P{1}, each List.Sum([y])}), {P{0}}) & hashtag#table(P, {{null,null}, {"Total " & P{1}, List.Sum(L[y])}})
in C
Power Query solution 14 for List the fruits and the, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Base = Table.TransformColumns(
Table.Combine(List.Transform(Table.Split(Source, 2), each Table.Transpose(_))),
{{"Column2", each try Number.From(_) otherwise null}}
),
FilterRows = Table.SelectRows(Base, each [Column2] <> null and [Column2] <> ""),
GroupRows = Table.Sort(
Table.Group(FilterRows, {"Column1"}, {{"Amount", each List.Sum([Column2]), type number}}),
{{"Column1", 0}}
),
RenCol = Table.RenameColumns(GroupRows, {{"Column1", "Fruits"}}),
Result = Table.InsertRows(
RenCol,
Table.RowCount(RenCol),
{[Fruits = null, Amount = null], [Fruits = "Total Amount", Amount = List.Sum(RenCol[Amount])]}
)
in
ResultPower Query solution 15 for List the fruits and the, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
List = Table.FromRows(
List.Transform(List.Combine(Table.ToColumns(Source)), each {_, _ is number})
),
AddIndex = Table.AddIndexColumn(List, "Index", 0, 1, Int64.Type),
AddFruit = Table.AddColumn(
AddIndex,
"Fruits",
each if [Column2] = true then AddIndex[Column1]{[Index] - 1} else null
),
RemCols = Table.SelectColumns(AddFruit, {"Fruits", "Column1"}),
FilterRows = Table.SelectRows(RemCols, each [Fruits] <> null and [Fruits] <> ""),
GrpRows = Table.Sort(
Table.Group(FilterRows, {"Fruits"}, {{"Amount", each List.Sum([Column1]), type number}}),
{"Fruits"}
),
Sol = GrpRows
& Table.FromRows(
{{null, null}, {"Total Amount", List.Sum(GrpRows[Amount])}},
Table.ColumnNames(GrpRows)
)
in
SolSolving the challenge of List the fruits and the with Excel
Excel solution 1 for List the fruits and the, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:D13,
g,
GROUPBY(
TOCOL(
IFS(
DROP(
z,
1
),
z
),
3
),
TOCOL(
IFS(
z,
z
),
3
),
SUM
),
VSTACK(
DROP(
g,
-1
),
{"",
""},
TAKE(
g,
-1
)
)
)Excel solution 2 for List the fruits and the, proposed by Rick Rothstein:
=LET(
r,
A2:D13,
f,
SORT(
UNIQUE(
TOCOL(
IF(
LEN(
r
)>3,
r,
z
),
3
)
)
),
a,
MAP(
f,
LAMBDA(
x,
SUM(
IF(
r=x,
OFFSET(
r,
1,
),
)
)
)
),
HSTACK(
VSTACK(
f,
"",
"Total"
),
VSTACK(
a,
"",
SUM(
a
)
)
)
)
With header...
=LET(
r,
A2:D13,
f,
SORT(
UNIQUE(
TOCOL(
IF(
LEN(
r
)>3,
r,
z
),
3
)
)
),
a,
MAP(
f,
LAMBDA(
x,
SUM(
IF(
r=x,
OFFSET(
r,
1,
),
)
)
)
),
HSTACK(
VSTACK(
"Fruits",
f,
"",
"Total"
),
VSTACK(
"Amount",
a,
"",
SUM(
a
)
)
)
)Excel solution 3 for List the fruits and the, proposed by 🇰🇷 Taeyong Shin:
=LET(d,
A2:D13,
L,
LAMBDA(f,
VSTACK(TOCOL(IFS((LEN(
d
)>1)*f(
d
),
d),
2),
UNICHAR(
1652
))),
GROUPBY(
L(
ISTEXT
),
L(
ISNUMBER
),
SUM
))Excel solution 4 for List the fruits and the, proposed by Kris Jaganah:
=LET(a,
TOCOL(
A2:D13,
3,
1
),
VSTACK({"Fruits",
"Amount"},
GROUPBY(a,
VSTACK(
DROP(
a,
1
),
""
),
SUM,
,
0,
,
(LEN(
a
)>1)*(ISTEXT(
a
))),
{"",
""},
HSTACK(
"Total Amount",
SUM(
a
)
)))Excel solution 5 for List the fruits and the, proposed by Oscar Mendez Roca Farell:
=LET(
d,
A2:D13,
m,
MOD(
ROW(
d
),
2
),
f,
TOCOL(
FILTER(
d,
m-1
)
),
GROUPBY(
f,
TOCOL(
FILTER(
d,
m
)
),
SUM,
,
1,
,
LEN(
f
)<>1
)
)Excel solution 6 for List the fruits and the, proposed by Duy Tùng:
=LET(F,
FILTER,
a,
TOCOL(
A2:D13,
1
),
u,
GROUPBY(F(a,
(a>"")*(LEN(
a
)>1)),
F(
a,
a<""
),
SUM,
,
0),
VSTACK(
EXPAND(
u,
ROWS(
u
)+1,
,
""
),
IF(
{1,
0},
"Total Amount",
SUM(
DROP(
u,
& ,
1
)
)
)
))Excel solution 7 for List the fruits and the, proposed by Sunny Baggu:
=LET(
_f,
SORT(
UNIQUE(
TOCOL(
IF(
ISTEXT(
A2:D13
) * LEN(
A2:D13
) > 1,
A2:D13,
1 / x
),
3
)
)
),
_a,
MAP(
_f,
LAMBDA(
a,
SUM(
TOCOL(
IF(
A2:D13 = a,
A3:D14,
1 / x
),
3
)
)
)
),
_b,
EXPAND(
"",
1,
2,
""
),
VSTACK(
HSTACK(
_f,
_a
),
_b,
HSTACK(
"Total Amount",
SUM(
_a
)
)
)
)Excel solution 8 for List the fruits and the, proposed by LEONARD OCHEA 🇷🇴:
=LET(
d,
A3:D13,
F,
LAMBDA(
x,
TOCOL(
IFS(
d,
x
),
3
)
),
GROUPBY(
F(
A2:D12
),
F(
d
),
SUM
)
)Excel solution 9 for List the fruits and the, proposed by Md. Zohurul Islam:
=LET(
z,
A2:D13,
sq,
SEQUENCE(
ROWS(
z
)
),
hdr,
HSTACK(
"Fruits",
"Amount"
),
a,
TOCOL(
FILTER(
z,
MOD(
sq,
2
)<>0
)
),
b,
TOCOL(
FILTER(
z,
MOD(
sq,
2
)=0
)
),
c,
FILTER(HSTACK(
a,
b
),
ISNUMBER(
b
)*(b>0)),
d,
GROUPBY(
TAKE(
c,
,
1
),
DROP(
c,
,
1
),
SUM,
0,
0
),
e,
HSTACK(
"Total Amount",
SUM(
DROP(
c,
,
1
)
)
),
f,
VSTACK(
hdr,
d,
{"",
""},
e
),
f)Excel solution 10 for List the fruits and the, proposed by Pieter de B.:
=LET(
a,
A2:D13,
d,
DROP,
L,
LAMBDA(
x,
TOCOL(
IFS(
DROP(
a,
1
),
x
),
2
)
),
g,
GROUPBY(
L(
d(
a,
-1
)
),
L(
d(
a,
1
)
),
SUM,
,
0
),
VSTACK(
g,
{"",
""},
HSTACK(
"Total Amount",
SUM(
d(
g,
,
1
)
)
)
)
)Excel solution 11 for List the fruits and the, proposed by Hamidi Hamid:
=LET(
h,
LAMBDA(
xx,
d,
CHOOSECOLS(
xx,
SEQUENCE(
COLUMNS(
xx
)/2,
,
d,
2
)
)
),
LET(
x,
WRAPCOLS(
TOCOL(
A2:D13
),
4
),
v,
h(
x,
1
),
w,
h(
x,
2
),
f,
HSTACK(
TOCOL(
v
),
TOCOL(
w
)
),
e,
GROUPBY(
TAKE(
f,
,
1
),
TAKE(
f,
,
-1
),
SUM,
),
g,
FILTER(
e,
TAKE(
e,
,
-1
)>0
),
fg,
VSTACK(
{"",
""},
HSTACK(
"Total Amount",
TAKE(
g,
-1,
-1
)
)
),
VSTACK(
DROP(
g,
-1
),
fg
)
)
)Excel solution 12 for List the fruits and the, proposed by Hamidi Hamid:
=LET(x,
TOCOL(
A2:D13
),
t,
UNIQUE(FILTER(x,
(ISTEXT(
x
)))),
g,
MAP(
t,
LAMBDA(
a,
SUMIF(
A2:D12,
a,
A3:D13
)
)
),
r,
HSTACK(
{"",
"Total Amount"},
VSTACK(
"",
SUM(
TAKE(
g,
,
-1
)
)
)
),
q,
VSTACK(
SORT(
HSTACK(
t,
g
),
1,
1
),
r
),
FILTER(
q,
TAKE(
q,
,
-1
)<>0
))Excel solution 13 for List the fruits and the, proposed by Asheesh Pahwa:
=LET(t,
TOCOL(IF((ISTEXT(
A2:D13
)*(LEN(
A2:D13
)=1)),
NA(),
A2:D13),
3,
1),
w,
WRAPROWS(
FILTER(
t,
t<>0
),
2
),
tk,
TAKE(
w,
,
1
),
u,
SORT(
UNIQUE(
tk
)
),
m,
MAP(
u,
LAMBDA(
x,
SUM(
--FILTER(
TAKE(
w,
,
-1
),
tk=x
)
)
)
),
VSTACK(
HSTACK(
u,
m
),
HSTACK(
"",
""
),
HSTACK(
"Total Amount",
SUM(
--TAKE(
w,
,
-1
)
)
)
))Excel solution 14 for List the fruits and the, proposed by Asheesh Pahwa:
=LET(
s,
TOCOL(
SCAN(
"",
A2:D13,
LAMBDA(
x,
y,
IF(
AND(
ISTEXT(
y
),
LEN(
y
)>1
),
y&"-"&OFFSET(
y,
1,
),
NA()
)
)
),
2
),
t,
TEXTBEFORE(
s,
"-"
),
_t,
TEXTAFTER(
s,
"-"
),
u,
SORT(
UNIQUE(
t
)
),
m,
MAP(
u,
LAMBDA(
x,
SUM(
--FILTER(
_t,
t=x
)
)
)
),
VSTACK(
HSTACK(
u,
m
),
HSTACK(
"",
""
),
HSTACK(
"Total Amount",
SUM(
--_t
)
)
)
)Excel solution 15 for List the fruits and the, proposed by ferhat CK:
=LET(
a,
TOCOL(
IF(
ISNUMBER(
A2:D13
),
OFFSET(
A2:D13,
-1,
)&"-"&A2:D13,
0
)
),
b,
FILTER(
a,
a>0
),
c,
GROUPBY(
TEXTBEFORE(
b,
"-"
),
--TEXTAFTER(
b,
"-"
),
SUM
),
VSTACK(
{"Fruits",
"Amount"},
DROP(
c,
-1
),
{"",
""},
TAKE(
c,
-1
)
)
)Excel solution 16 for List the fruits and the, proposed by Jaroslaw Kujawa:
=LET(
y;
A2:D13;
yy;
REDUCE(
"";
y;
LAMBDA(
a;
x;
IF(
LEN(
x
)<4+ISNUMBER(
MATCH(
x;
TAKE(
a;
;
1
);
0
)
);
a;
VSTACK(
a;
HSTACK(
x;
SUM(
IF(
y=x;
OFFSET(
x;
1;
)
)
)/COUNTIF(
y;
x
)
)
)
)
)
);
yyg;
DROP(
GROUPBY(
TAKE(
yy;
;
1
);
TAKE(
yy;
;
-1
);
SUM;
;
0
);
1
);
IFNA(
VSTACK(
{"Fruits","Amount"};
yyg;
{"",""};
"Total Amount"
);
SUM(
TAKE(
yyg;
;
-1
)
)
)
)Excel solution 17 for List the fruits and the, proposed by Ankur Sharma:
=LET(
p,
DROP,
a,
TOCOL(
A2:D13,
3,
TRUE
),
b,
FILTER(
a,
LEN(
a
) <> 1
),
c,
WRAPROWS(
b,
2
),
d,
GROUPBY(
p(
c,
,
-1
),
p(
c,
,
1
),
SUM
),
VSTACK(
p(
d,
-1
),
{"",
""},
TAKE(
d,
-1
)
)
)Excel solution 18 for List the fruits and the, proposed by Meganathan Elumalai:
=LET(f,
FILTER,
a,
TOCOL(
Table1,
1,
1
),
b,
GROUPBY(f(a,
(a>"")*(LEN(
a
)>1)),
f(
a,
a<""
),
SUM),
VSTACK(
DROP(
b,
-1
),
{"",
""},
TAKE(
b,
-1
)
))Excel solution 19 for List the fruits and the, proposed by Imam Hambali:
=LET(
d,
A2:D13,
n,
MOD(
SEQUENCE(
ROWS(
d
)
),
2
),
l,
LAMBDA(
x,
TOCOL(
FILTER(
d,
n=x
)
)
),
f,
FILTER(
HSTACK(
l(
1
),
l(
0
)
),
LEN(
l(
1
)
)>1
),
cc,
CHOOSECOLS,
gb,
GROUPBY(
cc(
f,
1
),
cc(
f,
2
),
SUM,
,
1
),
t,
VSTACK(
{"Fruits",
"Amount"},
DROP(
gb,
-1
),
{"",
""},
TAKE(
gb,
-1
)
),
IF(
t="Total",
t&" Amount",
t
)
)Excel solution 20 for List the fruits and the, proposed by Eddy Wijaya:
=LET(d,
REDUCE(G1:H1,
A2:D13,
LAMBDA(a,
v,
VSTACK(a,
LET(m,
IF((LEN(
v
)>1)*(ISTEXT(
v
)),
v&","&OFFSET(
v,
1,
),
""),
IFERROR(
TEXTSPLIT(
m,
","
),
m
))))),
c,
FILTER(
d,
TAKE(
d,
,
1
)<>""
),
VSTACK(
IFERROR(
--c,
c
),
{"",
""},
HSTACK(
G10,
BYCOL(
--DROP(
TAKE(
c,
,
-1
),
1
),
SUM
)
)
))Excel solution 21 for List the fruits and the, proposed by Milan Shrimali:
=LET(
A,
MAP(
A2:D13,
LAMBDA(
X,
IFERROR(
IF(
AND(
IS NUMBER(
OFFSET(
X,
1,
0
)
),
IS TEXT(
X
)
),
JOIN(
"-",
X,
OFFSET(
X,
1,
0
)
),
""
),
""
)
)
),
B,
ARRAYFORMULA(
SPLIT(
UNIQUE(
TOCOL(
A
),
0,
1
),
"-"
)
),
FNL,
SORT(
BYROW(
UNIQUE(
CHOOSECOLS(
B,
1
)
),
LAMBDA(
X,
HSTACK(
X,
SUM(
FILTER(
CHOOSECOLS(
B,
2
),
CHOOSECOLS(
B,
1
)=X
)
)
)
)
),
1,
1
),
IFERROR(
VSTACK(
FNL,
"",
HSTACK(
"TOTAL",
SUM(
CHOOSECOLS(
FNL,
2
)
)
)
),
""
)
)Excel solution 22 for List the fruits and the, proposed by Peter Bartholomew:
=LET(
data, WRAPROWS(TOCOL(table, , TRUE), 2),
criterion, REGEXTEST(TAKE(data, , 1), "w{2,}"),
cleanData, FILTER(data, criterion),
fruit, VSTACK("Fruits", TAKE(cleanData, , 1)),
quantity, VSTACK("Amount", TAKE(cleanData, , -1)),
GROUPBY(fruit, quantity, SUM, 3)
)Excel solution 23 for List the fruits and the, proposed by Ahmed Ariem:
=LET(
arry,
WRAPROWS(
TOCOL(
IF(
LEN(
A2:D13
)>1,
A2:D13,
""
),
1,
TRUE
),
2
),
GROUPBY(
CHOOSECOLS(
arry,
1
),
CHOOSECOLS(
arry,
2
),
SUM,
,
,
,
CHOOSECOLS(
arry,
1
)<>""
)
)Excel solution 24 for List the fruits and the, proposed by Ricardo Romero Garcia:
=LET(
z;
ELEGIRCOLS;
m;
AJUSTARFILAS(
ENCOL(
A2:D13;
0;
1
);
2
);
a;
FILTRAR(
m;
LARGO(
z(
m;
1
)
)>1
);
b;
AGRUPARPOR(
z(
a;
1
);
z(
a;
2
);
SUMA;
;
0
);
c;
REPETIR(
"";
SECUENCIA(
;
2
)
);
d;
APILARH(
"Total Amount";
SUMA(
z(
b;
2
)
)
);
e;
APILARH(
"Fruits";
"Amount"
);
APILARV(
e;
b;
c;
d
)
)Excel solution 25 for List the fruits and the, proposed by abdelaziz kamal allam:
=LET(
d,
WRAPROWS(
TOCOL(
TRANSPOSE(
A2:D13
)
),
2
),
x,
FILTER(
d,
ISNUMBER(
CHOOSECOLS(
d,
2
)
)
),
m,
MAP(
UNIQUE(
CHOOSECOLS(
x,
1
)
),
LAMBDA(
a,
SUM(
FILTER(
CHOOSECOLS(
x,
2
),
CHOOSECOLS(
x,
1
)=a
)
)
)
),
VSTACK(
VSTACK(
{"Fruits",
"Amount"},
HSTACK(
UNIQUE(
CHOOSECOLS(
x,
1
)
),
m
)
),
CHOOSE(
{1,
2},
"Total Amount",
SUM(
m
)
)
)
)Solving the challenge of List the fruits and the with Python
Python solution 1 for List the fruits and the, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_259.xlsx"
input = pd.read_excel(path, usecols="A:D", nrows=13)
test = pd.read_excel(path, usecols="G:H", nrows=9)
def process_rows(data, filter_cond):
filtered_data = data.iloc[::2] if filter_cond == 1 else data.iloc[1::2]
filtered_data = pd.concat([filtered_data.iloc[i] for i in range(len(filtered_data))], axis=0)
return filtered_data.reset_index(drop=True).T
odd_rows = process_rows(input, 1)
even_rows = process_rows(input, 0)
output = pd.concat([odd_rows, even_rows], axis=1)
output.columns = ["Fruits", "Amount"]
output = output.dropna().query('Fruits.str.len() > 1')
output['Amount'] = pd.to_numeric(output['Amount'])
output = output.groupby('Fruits', as_index=False)['Amount'].sum().sort_values('Fruits')
total = pd.DataFrame([["Total Amount", output['Amount'].sum()]], columns=["Fruits", "Amount"])
result = pd.concat([output, pd.DataFrame([[np.NaN, np.NaN]], columns=["Fruits", "Amount"]), total], ignore_index=True)
result['Amount'] = result['Amount'].astype(np.float64)
print(result.equals(test)) # True
Python solution 2 for List the fruits and the, proposed by Luan Rodrigues:
import pandas as pd
import numpy as np
file = "PQ_Challenge_259.xlsx"
df = pd.read_excel(file,usecols="A:D")
df = [df[col].to_list() for col in df.columns]
df = [str(x) for x in np.concatenate(df) if x != 'nan' and len(x) != 1]
df = pd.DataFrame([df[i:i + 2] for i in range(0,len(df),2) ],columns=['Fruits','Amount'])
grp = df.groupby('Fruits')['Amount'].sum().reset_index()
df_fim = pd.concat([grp,pd.DataFrame([[None,None],['Total Amount',int(grp['Amount'].astype('int').sum())]],columns=grp.columns)])
print(df_fim)
Python solution 3 for List the fruits and the, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_259.xlsx'
df = pd.read_excel(io=file_path, usecols='A:D')
# Perform data manipulation
values = []
for i in range(0, len(df), 2):
values.extend(zip(df.iloc[i], df.iloc[i + 1]))
df = pd.DataFrame(values, columns=['Fruits', 'Amount'])
df = df[df['Fruits'].str.len() > 1]
df = df.groupby('Fruits', as_index=False)['Amount'].sum()
total = df.Amount.sum()
df.loc[len(df)] = ['', '']
df.loc[len(df)] = ['Total Amount', total]
df
Solving the challenge of List the fruits and the with Python in Excel
Python in Excel solution 1 for List the fruits and the, proposed by Francesco Bianchi 🇮🇹:
df =xl("Sheet1!$A$2:$D$13")
df = df.melt(var_name='Column', value_name='Amount')
df.dropna(inplace=True)
df['n'] = pd.to_numeric(df['Amount'], errors='coerce').notna()
df['Fruits']= [f if v == True else np.nan for f, v in zip(df['Amount'].shift(),df['n'])]
df = df[['Fruits','Amount']]
df.dropna(inplace=True)
grp =df.groupby('Fruits').sum()
grp = grp.astype('int32')
total_amount = grp['Amount'].sum()
grp.loc[''] = ['']
grp.loc['Total Amount'] = [total_amount]
grp.reset_index(inplace=True)
grp
Solving the challenge of List the fruits and the with R
R solution 1 for List the fruits and the, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_259.xlsx"
input = read_excel(path, range = "A1:D13")
test = read_excel(path, range = "G1:H10")
process_rows = function(data, filter_cond) {
data %>%
filter(row_number() %% 2 == filter_cond) %>%
split(1:nrow(.)) %>%
bind_cols() %>%
t() %>%
as.data.frame()
}
odd_rows = process_rows(input, 1)
even_rows = process_rows(input, 0)
output = bind_cols(odd_rows, even_rows) %>%
set_names(c("Fruits", "Amount")) %>%
filter(!is.na(Fruits) & nchar(Fruits) > 1) %>%
mutate(Amount = as.numeric(Amount)) %>%
group_by(Fruits) %>%
summarise(Amount = sum(Amount)) %>%
arrange(Fruits)
total = tibble(Fruits = "Total Amount", Amount = sum(output$Amount))
result = bind_rows(output, tibble(Fruits = NA, Amount = NA), total)
all.equal(result, test, check.attributes = FALSE)
# TRUE
&