A row appears for an alphabet if seq is continuous. When continuity breaks, a new row appears. Missing column documents what numbers were missed between current and last row populated.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 42
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Sales Figures Per Category with Power Query
Power Query solution 1 for Generate Sales Figures Per Category, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Offset = Table.FromColumns(
Table.ToColumns(Source)
& {{99} & List.Transform(List.RemoveLastN(Source[Seq]), each _ + 1)}
& {{""} & List.RemoveLastN(Source[Alphabets])},
{"A", "X", "Y", "B"}
),
Check = Table.AddColumn(Offset, "C", each [A] <> [B] or [X] <> [Y]),
Grouped = Table.FromRows(
Table.Group(
Check,
"C",
{
"T",
each {
[A]{0},
[X]{0},
List.Last([X]),
if [X]{0} > [Y]{0} then
Text.Combine(List.Transform({[Y]{0} .. [X]{0} - 1}, Text.From), ",")
else
null
}
},
0,
(b, e) => Number.From(e)
)[T],
{"Alphabet", "From", "To", "Missing"}
)
in
GroupedPower Query solution 2 for Generate Sales Figures Per Category, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped1 = Table.Group(
Source,
{"Alphabets"},
{
{
"Count",
each
let
a = _,
b = Table.AddIndexColumn(a, "Idx"),
c = Table.AddColumn(b, "next", each try b[Seq]{[Idx] - 1} + 1 otherwise 0),
d = Table.FillDown(
Table.AddColumn(c, "From", each if [Seq] - [next] > 0 then [Seq] else null),
{"From"}
)
in
d
}
}
)[[Count]],
Combine = Table.Combine(Grouped1[Count]),
Grouped2 = Table.Group(Combine, {"Alphabets", "From"}, {{"To", each List.Last([Seq])}}),
Index = Table.AddIndexColumn(Grouped2, "Index", 0, 1, Int64.Type),
Missing = Table.AddColumn(
Index,
"Missing",
each try
if Index[Alphabets]{[Index] - 1} = Index[Alphabets]{[Index]} then
{Index[To]{[Index] - 1} + 1 .. [From] - 1}
else
null
otherwise
null
),
MissingCom = Table.TransformColumns(
Missing,
{"Missing", each try Text.Combine(List.Transform(_, Text.From), ",") otherwise "", type text}
),
Solucion = Table.RemoveColumns(MissingCom, {"Index"})
in
SolucionPower Query solution 3 for Generate Sales Figures Per Category, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fn = (S, T) =>
let
C_1 = List.Generate(
() => [x = 1, y = 0, Missing = {}],
each [y] < List.Count(S),
each [
y = [y] + 1,
x = if y = 0 then [x] else if S{y} - S{[y]} = 1 then [x] else [x] + 1,
Missing = {S{[y]} + 1 .. S{y} - 1}
],
each [[x], [Missing]]
),
C_2 = Table.FromColumns(Table.ToColumns(T) & {C_1}, Table.ColumnNames(T) & {"Helper"}),
C_3 = Table.ExpandRecordColumn(C_2, "Helper", {"x", "Missing"}, {"x", "Missing"}),
C_4 = Table.Group(
C_3,
{"x"},
{
{"Alphabets", each List.Distinct([Alphabets]){0}},
{"From", each List.Min([Seq])},
{"To", each List.Max([Seq])},
{
"Missing",
each Text.Combine(
List.Transform(
List.Combine(List.Select([Missing], each not List.IsEmpty(_))),
Text.From
),
","
)
}
}
),
C_5 = Table.RemoveColumns(C_4, {"x"})
in
C_5,
GroupedWithFn = Table.Combine(
Table.Group(Source, {"Alphabets"}, {{"All", each Fn([Seq], _)}})[All]
),
ExpectedOutput = Table.ReplaceValue(GroupedWithFn, "", null, Replacer.ReplaceValue, {"Missing"})
in
ExpectedOutputPower Query solution 4 for Generate Sales Figures Per Category, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData42"]}[Content],
Group = Table.Group(
Source,
{"Alphabets"},
{
"Data",
each
let
_Seq = List.Buffer(_[Seq]),
_Transform = List.Accumulate(
_[Seq],
[R = {}, From = _Seq{0}],
(s, c) =>
let
_From = if (List.Contains(_Seq, c - 1)) then s[From] else c,
_NewR =
if (List.Contains(_Seq, c + 1)) then
s[R]
else
let
_PrevTo = if (List.Count(s[R]) > 0) then List.Last(s[R])[To] + 1 else c,
_Missed = Text.Combine(
List.Transform({_PrevTo .. (_From - 1)}, Text.From),
","
)
in
s[R] & {[From = _From, To = c, Missed = _Missed]}
in
[From = _From, R = _NewR]
)
in
Table.FromRecords(_Transform[R])
}
),
Expand = Table.ExpandTableColumn(Group, "Data", Table.ColumnNames(Group[Data]{0}))
in
ExpandPower Query solution 5 for Generate Sales Figures Per Category, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Seq"]}[Content],
Shift = Table.FromColumns(
Table.ToColumns(Source) & {{"-"} & List.RemoveLastN(Source[Alphabets], 1)} & {{0} & List.RemoveLastN(Source[Seq], 1)},
Table.ColumnNames(Source) & {"Shifted Alpha"} & {"Shifted Seq"}
),
From = Table.AddColumn(
Shift,
"From",
each
if [Seq] - [Shifted Seq] <> 1
or [Alphabets] <> [Shifted Alpha]
then [Seq]
else null
),
#"Filled Down" = Table.FillDown(From, {"From"}),
#"Grouped Rows" = Table.Group(
#"Filled Down",
{"Alphabets", "From"},
{
{"To", each List.Max([Seq]), type number},
{"Missing",
each
Text.Combine(
List.Transform({_{0}[Shifted Seq] + 1 .. _{0}[Seq] - 1}, each Text.From(_)),
","
)
}
}
)
in
#"Grouped Rows"
*Imke Feldmann s. link in the comments
Power Query solution 6 for Generate Sales Figures Per Category, proposed by Owen Price:
https://gist.github.com/ncalm/395698fae6452db0097117aaeb73408d
Power Query solution 7 for Generate Sales Figures Per Category, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Accumulate = List.Accumulate(
Table.ToRecords(Source),
[last = 0, alpha = "", thelist = {}],
(s, c) =>
if List.IsEmpty(s[thelist]) then
[last = c[Seq], alpha = c[Alphabets], thelist = {{c}}]
else if c[Seq] = s[last] + 1 and c[Alphabets] = s[alpha] then
[
last = c[Seq],
alpha = c[Alphabets],
thelist = List.RemoveLastN(s[thelist]) & {List.Last(s[thelist]) & {c}}
]
else
[last = c[Seq], alpha = c[Alphabets], thelist = s[thelist] & {{c}}]
),
Transform = List.Transform(
Accumulate[thelist],
each
let
t = Table.FromRecords(_)
in
[Alphabets = t[Alphabets]{0}, From = List.Min(t[Seq]), To = List.Max(t[Seq])]
),
ToTable = Table.FromRecords(Transform),
Index = Table.AddIndexColumn(ToTable, "Index", 0, 1, Int64.Type),
Missing = Table.AddColumn(
Index,
"Missing",
each try
Text.Combine(
List.Transform(
List.RemoveLastN(List.Skip({Index[To]{[Index] - 1} .. Index[From]{[Index]}})),
Text.From
),
", "
)
otherwise
""
),
Remove = Table.RemoveColumns(Missing, {"Index"})
in
RemoveSolving the challenge of Generate Sales Figures Per Category with Excel
Excel solution 1 for Generate Sales Figures Per Category, proposed by Bo Rydobon 🇹🇭:
=LET(z,
A2:B15,
SORTBY(REDUCE({"Alphabets",
"Missing",
"From",
"To"},
SEQUENCE(
ROWS(
z
)
),
LAMBDA(a,
x,
LET(n,
INDEX(
z,
x,
2
),
b,
INDEX(
z,
x,
1
),
l,
INDEX(
a,
ROWS(
a
),
4
),
d,
n-1-N(
l
),
IF((l=n-1)*IF(
n>1,
b=INDEX(
z,
x-1,
1
)
),
VSTACK(
DROP(
a,
-1
),
HSTACK(
TAKE(
a,
-1,
3
),
n
)
),
VSTACK(
a,
HSTACK(
b,
IF(
d>0,
TEXTJOIN(
",",
,
SEQUENCE(
d,
,
l+1
)
),
""
),
n,
n
)
))))),
{1,
4,
2,
3}))Excel solution 2 for Generate Sales Figures Per Category, proposed by Bo Rydobon 🇹🇭:
=SORTBY(REDUCE({"Alphabets",
"Missing",
"From",
"To"},
B2:B15,
LAMBDA(a,
n,
LET(l,
INDEX(
a,
ROWS(
a
),
4
),
d,
n-1-N(
l
),
IF((l=n-1)*(OFFSET(
n,
-1,
-1
)=OFFSET(
n,
,
-1
)),
VSTACK(
DROP(
a,
-1
),
HSTACK(
TAKE(
a,
-1,
3
),
n
)
),
VSTACK(
a,
HSTACK(
OFFSET(
n,
,
-1
),
IF(
d>0,
TEXTJOIN(
",",
,
SEQUENCE(
d,
,
l+1
)
),
""
),
n,
n
)
))))),
{1,
4,
2,
3})Excel solution 3 for Generate Sales Figures Per Category, proposed by محمد حلمي:
=LET(
q,
SCAN(
,
B2:B15=N(
+B1:B14
)+1,
LAMBDA(
a,
d,
IF(
d,
a,
a+1
)
)
),
REDUCE({"Alphabets",
"To",
"From",
"Missing"},
UNIQUE(
q
),
LAMBDA(a,
d,
LET(
e,
FILTER(
A2:B15,
q=d
),
w,
TAKE(
e,
1,
1
),
c,
MIN(
e
),
i,
TAKE(
a,
-1
),
v,
MAX(
i
),
VSTACK(a,
HSTACK(w,
c,
MAX(
e
),
ARRAYTOTEXT(IF((w=TAKE(
a,
-1,
1
))*(c<>MIN(
i
)),
SEQUENCE(
c-v-1,
,
v+1
),
""))))))))Excel solution 4 for Generate Sales Figures Per Category, proposed by محمد حلمي:
=LET(
M,
MAP(A1:A14,
A2:A15,
B1:B14,
B2:B15,
LAMBDA(a,
b,
c,
d,
IF((b=a)*(d-1<>c),
TEXTJOIN(
",",
,
SEQUENCE(
d-c-1,
,
c+1
)
),
""))),
h,
VSTACK(0,
SCAN(0,
B3:B15=(B2:B14+1)*(A3:A15=A2:A14),
LAMBDA(
A,
D,
IF(
D,
A,
A+1
)
))),
REDUCE(
{"Alphabets",
"From",
"To",
"Missing"},
UNIQUE(
h
),
LAMBDA(
a,
d,
LET(
e,
FILTER(
HSTACK(
A2:B15,
M
),
h=d
),
VSTACK(
a,
TAKE(
HSTACK(
TAKE(
e,
,
1
),
MIN(
e
),
MAX(
e
),
TAKE(
e,
,
-1
)
),
1
)
)
)
)
))Excel solution 5 for Generate Sales Figures Per Category, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:B15,
_al,
TAKE(
_d,
,
1
),
_sq,
TAKE(
_d,
,
-1
),
_c1,
DROP(
_sq,
-1
) + 1 <> DROP(
_sq,
1
),
_c2,
DROP(
_al,
-1
) <> DROP(
_al,
1
),
_e1,
LAMBDA(a,
b,
a + (b > 0)),
_c3,
VSTACK(
1,
SCAN(
1,
_c1 + _c2,
_e1
)
),
_c4,
UNIQUE(
_c3
),
_fn,
XLOOKUP(
_c4,
_c3,
_al
),
_ftf,
XLOOKUP(
_c4,
_c3,
_sq,
,
,
{1,
-1}
),
_c5,
DROP(
_ftf,
1,
-1
) - DROP(
_ftf,
-1,
1
) - 1,
_e2,
LAMBDA(
a,
b,
ARRAYTOTEXT(
IFERROR(
SEQUENCE(
a,
,
b + 1
),
""
)
)
),
_c6,
VSTACK(
"",
MAP(
_c5,
DROP(
_ftf,
-1,
1
),
_e2
)
),
_r,
HSTACK(
_fn,
_ftf,
_c6
),
_r
)Solving the challenge of Generate Sales Figures Per Category with Python
Python solution 1 for Generate Sales Figures Per Category, proposed by Igor Perković:
Masking(ball) 😀
https://gist.github.com/igorp74/ed901f091043834aafe0aac672f3a4e3Solving the challenge of Generate Sales Figures Per Category with SQL
SQL solution 1 for Generate Sales Figures Per Category, proposed by Zoran Milokanović:
1/2
WITH -- ORACLE 11G
DATA_PREP
AS
(
SELECT
MIN(F.ORDERING) OVER (PARTITION BY F.ALPHABETS) AS ALPHABETS_ORDER
,F.ALPHABETS
,F.SEQ
FROM
(
SELECT
T.ALPHABETS
,T.SEQ
,ROW_NUMBER() OVER (ORDER BY T.TEMP) AS ORDERING
FROM
(
SELECT
1 AS TEMP
,D.ALPHABETS
,D.SEQ
FROM DATA D
) T
) F
),
EXTENSION (ALPHABETS_ORDER, ALPHABETS, SEQ_START, SEQ_END)
AS
(
SELECT
DP.ALPHABETS_ORDER
,DP.ALPHABETS
,MIN(DP.SEQ) AS SEQ_START
,MAX(DP.SEQ) AS SEQ_END
FROM DATA_PREP DP
GROUP BY
DP.ALPHABETS_ORDER
,DP.ALPHABETS
UNION ALL
SELECT
E.ALPHABETS_ORDER
,E.ALPHABETS
,E.SEQ_START + 1 AS SEQ_START
,E.SEQ_END
FROM EXTENSION E
WHERE
E.SEQ_START < E.SEQ_END
),
INTERMEDIATE
AS
(
SELECT
I2.ALPHABETS_ORDER
,I2.ALPHABETS
,I2.SEQ
,I2.DATA_FLAG
(PARTITION BY I2.ALPHABETS, I2.DATA_FLAG ORDER BY I2.SEQ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) A&S SEQ_ID
FROM
(
&&