Transpose the problem table into result table as shown.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 240
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Spread Yearly Rows to Columns with Power Query
Power Query solution 1 for Spread Yearly Rows to Columns, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
H = Table.ColumnNames(Source),
P = Table.Sort(
Table.Pivot(
Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each Text.Split(_{2}, ", "),
(i, _) => {i{0}, i{1}} & Text.Split(_, ": ")
),
{H{0}, H{1}, "A", "V"}
),
{"Bread", "Coke", "Milk", "Rice"},
"A",
"V",
each Number.From(List.Max(_))
),
{H{0}, {H{1}, 1}}
)
in
PPower Query solution 2 for Spread Yearly Rows to Columns, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ExpandListColumn(
Table.TransformColumns(A, {"Items", Splitter.SplitTextByDelimiter(", ")}),
"Items"
),
C = Table.SplitColumn(B, "Items", Splitter.SplitTextByDelimiter(": "), {"Items.1", "Items.2"}),
D = Table.Pivot(C, List.Sort(List.Distinct(C[Items.1])), "Items.1", "Items.2"),
E = Table.Sort(D, {{"Supplier", 0}, {"Date", 1}})
in
EPower Query solution 3 for Spread Yearly Rows to Columns, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Split1 = Table.TransformColumns(Source, {"Items", each Text.Split(_, ", ")}),
Expand = Table.ExpandListColumn(Split1, "Items"),
Split2 = Table.SplitColumn(Expand, "Items", each Text.Split(_, ": "), {"I", "A"}),
Pivot = Table.Pivot(
Split2,
List.Sort(List.Distinct(Split2[I])),
"I",
"A",
each Number.From(_{0}?)
),
Return = Table.Sort(Pivot, {"Supplier", {"Date", 1}})
in
ReturnPower Query solution 4 for Spread Yearly Rows to Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Pross = Table.TransformColumns(
Source,
{
{
"Items",
each
let
a = List.Select(Text.SplitAny(_, ",: "), each _ <> ""),
b = List.Transform(List.Split(a, 2), each {_{0}, Number.From(_{1})}),
c = Table.PromoteHeaders(Table.Transpose(Table.FromRows(b)))
in
c
}
}
),
Exp = Table.ExpandTableColumn(
Pross,
"Items",
List.Sort(Table.ColumnNames(Table.Combine(Pross[Items])))
),
Sol = Table.Sort(Exp, {{"Supplier", 0}})
in
SolPower Query solution 5 for Spread Yearly Rows to Columns, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"tab",
each
let
a = Text.Split([Items], ", "),
b = Table.PromoteHeaders(Table.FromColumns(List.Transform(a, (x) => Text.Split(x, ":")))),
t = {[Supplier], [Date]},
d = List.Accumulate(
{0 .. 1},
b,
(s, c) => Table.AddColumn(s, {"Supplier", "Date"}{c}, (x) => t{c})
)
in
d
)[tab],
cmb = Table.Combine(add),
cab = {"Supplier", "Date"}
& List.Sort(List.RemoveMatchingItems(Table.ColumnNames(cmb), {"Supplier", "Date"})),
sel = Table.SelectColumns(cmb, cab),
cls = Table.Sort(sel, {"Supplier"})
in
clsPower Query solution 6 for Spread Yearly Rows to Columns, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform1 = Table.TransformColumns(Source, {"Items", each Text.Split(_, ", ")}),
Expand = Table.ExpandListColumn(Transform1, "Items"),
Split = Table.SplitColumn(Expand, "Items", each Text.Split(_, ": "), {"Item", "Value"}),
SortedCols = List.Distinct(List.Sort(Split[Item])),
Pivot = Table.Pivot(Split, SortedCols, "Item", "Value"),
TransformedCols = List.Transform(SortedCols, each {_, type number}),
Transform2 = Table.TransformColumnTypes(Pivot, {{"Date", type date}} & TransformedCols),
Result = Table.Sort(Transform2, {{"Supplier", 0}, {"Date", 1}})
in
ResultPower Query solution 7 for Spread Yearly Rows to Columns, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = [
a = List.Transform(Source[Items], each Text.SplitAny(Text.Remove(_, " "), ":,")),
b = List.Transform(a, each List.Alternate(_, 1, 1, 1)),
c = List.Distinct(List.Sort(List.Combine(b)))
][c],
Transform = Table.TransformColumns(
Source,
{
"Items",
each List.Accumulate(
Text.Split(_, ", "),
[],
(s, c) => [u = Text.Split(c, ": "), v = Record.AddField(s, u{0}, u{1})][v]
)
}
),
Sort = Table.Sort(Table.ExpandRecordColumn(Transform, "Items", ColNames), "Supplier"),
Result = Table.TransformColumnTypes(Sort, {"Date", type date})
in
ResultPower Query solution 8 for Spread Yearly Rows to Columns, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ExpandListColumn(
Table.TransformColumns(
S,
{
"Items",
Splitter.SplitTextByDelimiter(", "),
let
itemType = (type nullable text)
in
type {itemType}
}
),
"Items"
),
b = Table.SplitColumn(a, "Items", Splitter.SplitTextByDelimiter(": "), {"I1", "I2"}),
c = Table.Sort(b, {"I1", 0}),
d = Table.Pivot(c, List.Distinct(c[I1]), "I1", "I2"),
Sol = Table.Sort(d, {{"Supplier", 0}, {"Date", 1}})
in
SolPower Query solution 9 for Spread Yearly Rows to Columns, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData240"]}[Content],
Transform = Table.TransformColumns(
Source,
{
"Items",
each
let
cv = List.Combine(List.Transform(Text.Split(_, ", "), each Text.Split(_, ": ")))
in
Table.FromRows({List.Alternate(cv, 1, 1)}, List.Alternate(cv, 1, 1, 1))
}
),
CNames = List.Sort(
List.Distinct(List.Combine(List.Transform(Transform[Items], each Table.ColumnNames(_))))
),
Expand = Table.ExpandTableColumn(Transform, "Items", CNames)
in
ExpandPower Query solution 10 for Spread Yearly Rows to Columns, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S,
"Deli",
each Text.ToList(Text.Remove(Text.Lower([Items]), {"a" .. "z", "0" .. "9", " "}))
),
Deli = List.Distinct(List.Combine(A[Deli])),
B = Table.AddColumn(
S,
"L",
each Table.FromColumns(
{
List.Alternate(Splitter.SplitTextByAnyDelimiter(Deli)([Items]), 1, 1, 1),
List.Alternate(Splitter.SplitTextByAnyDelimiter(Deli)([Items]), 1, 1)
},
{"A", "V"}
)
),
C = Table.ExpandTableColumn(B, "L", {"A", "V"}, {"A", "V"}),
D = Table.SelectColumns(C, {"Supplier", "Date", "A", "V"}),
E = Table.TransformColumns(D, {{"A", Text.Trim, type text}, {"V", Text.Trim, type text}}),
F = Table.TransformColumnTypes(E, {{"V", Int64.Type}}),
G = Table.Pivot(F, List.Sort(List.Distinct(F[A])), "A", "V", List.Sum),
H = Table.Sort(G, {{"Supplier", Order.Ascending}, {"Date", Order.Descending}})
in
HPower Query solution 11 for Spread Yearly Rows to Columns, proposed by Peter Krkos:
let
Transformed = Table.Combine(
Table.AddColumn(
Source,
"T",
each List.Accumulate(
List.Split(Text.SplitAny([Items], ":,"), 2),
Table.FromRecords({Record.RemoveFields(_, {"Items"})}),
(s, c) => Table.AddColumn(s, Text.Trim(c{0}), (x) => Int64.From(c{1}), Int64.Type)
)
)[T]
),
Reordered = Table.ReorderColumns(
Transformed,
let
a = Table.ColumnNames(Transformed)
in
List.FirstN(a, 2) & List.Sort(List.Skip(a, 2))
)
in
ReorderedPower Query solution 12 for Spread Yearly Rows to Columns, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
SplitByComma = Table.ExpandListColumn(
Table.TransformColumns(
Source,
{
{
"Items",
Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Items"
),
SplitByColon = Table.SplitColumn(
SplitByComma,
"Items",
Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv),
{"Items", "Price"}
),
SortRows = Table.Sort(SplitByColon, {{"Items", Order.Ascending}}),
ChangeType = Table.TransformColumnTypes(SortRows, {{"Price", type number}}),
PivotColumn = Table.Pivot(
ChangeType,
List.Distinct(ChangeType[Items]),
"Items",
"Price",
List.Sum
)
in
PivotColumnPower Query solution 13 for Spread Yearly Rows to Columns, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WclTSUTIyMDLRNTTUNbIEcnwzc7KtFIxNDHQUnIpSE1OsFExNDZRidaKVnNCVQuWNjIBqgzKTU60ULEzAKp3RVTrnZ4NlIQah2GmAZKcRwk4TU7iZhpi2GyDZboiw3QDTdgMk203hxhsZmoKVuuBQag40E+IoIyOgylgA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Supplier = _t, Date = _t, Items = _t]
),
GetTab = Table.ExpandTableColumn(
Table.TransformColumns(
Source,
{
"Items",
each Table.FromRows(List.Split(Text.SplitAny(Text.Remove(_, " "), ",: "), 2), {"A", "V"})
}
),
"Items",
{"A", "V"}
),
ChanType = Table.TransformColumnTypes(GetTab, {{"Date", type date}, {"V", type number}}),
PivotCol = Table.Pivot(ChanType, List.Sort(List.Distinct(ChanType[A])), "A", "V", List.Sum),
SortRows = Table.Sort(PivotCol, {{"Supplier", 0}, {"Date", 1}})
in
SortRowsPower Query solution 14 for Spread Yearly Rows to Columns, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Supplier", type text}, {"Date", type datetime}, {"Items", type text}}
),
Records = Table.AddColumn(
#"Changed Type",
"AllFields",
each [
P1 = _[[Supplier], [Date]],
Items = Text.Split([Items], ", "),
P2a = List.Transform(Items, each Text.BeforeDelimiter(_, ":")),
P2b = List.Transform(Items, each Text.AfterDelimiter(_, ":")),
P2c = Record.FromList(P2b, P2a),
Rec = P1 & P2c
][Rec],
type record
)[AllFields],
Cols = List.Union(List.Transform(Records, Record.FieldNames)),
Table = Table.FromRecords(Records, Cols, MissingField.UseNull),
#"Sorted Rows" = Table.Sort(Table, {{"Supplier", Order.Ascending}, {"Date", Order.Ascending}}),
#"Changed Type1" = Table.TransformColumnTypes(
#"Sorted Rows",
{{"Supplier", type text}, {"Date", type date}}
)
in
#"Changed Type1"Power Query solution 15 for Spread Yearly Rows to Columns, proposed by Joevan Bedico:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer =
let
s = Source,
l = List.Transform,
a = l(s[Items], each List.Split(Text.SplitAny(Text.Replace(_, " ", ""), ":,"), 2)),
b = List.Sort(List.Distinct(l(List.Combine(a), List.First)))
in
Table.Sort(
Table.RemoveColumns(
Table.FromRows(
l(
List.Zip(
{
Table.ToRows(s),
l(
a,
each l(List.ReplaceMatchingItems(b, _), each try Number.From(_) otherwise null)
)
}
),
List.Combine
),
Table.ColumnNames(s) & b
),
"Items"
),
{"Supplier", 0}
)
in
AnswerPower Query solution 16 for Spread Yearly Rows to Columns, proposed by abdelaziz kamal allam:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Supplier", type text}, {"Date", type datetime}, {"Items", type text}}
),
#"Split Column by Delimiter" = Table.ExpandListColumn(
Table.TransformColumns(
#"Changed Type",
{
{
"Items",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Items"
),
#"Split Column by Delimiter1" = Table.SplitColumn(
#"Split Column by Delimiter",
"Items",
Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),
{"Items.1", "Items.2"}
),
#"Trimmed Text" = Table.TransformColumns(
#"Split Column by Delimiter1",
{{"Items.1", Text.Trim, type text}}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Trimmed Text",
{{"Items.1", type text}, {"Items.2", Int64.Type}, {"Date", type date}}
),
#"Pivoted Column" = Table.Pivot(
#"Changed Type1",
List.Distinct(#"Changed Type1"[Items.1]),
"Items.1",
"Items.2",
List.Sum
)
in
#"Pivoted Column"Solving the challenge of Spread Yearly Rows to Columns with Excel
Excel solution 1 for Spread Yearly Rows to Columns, proposed by Bo Rydobon 🇹🇭:
=LET(
r,
LAMBDA(
p,
REGEXEXTRACT(
TEXTSPLIT(
TEXTJOIN(
"|",
,
C2:C8
),
", ",
"|"
),
p
)
),
n,
--r(
"d+"
),
C,
LAMBDA(
a,
TOCOL(
IFS(
n,
a
),
3
)
),
PIVOTBY(
HSTACK(
C(
A2:A8
),
C(
B2:B8
)
),
C(
r(
"pl+"
)
),
C(
n
),
MAX,
,
0,
-2,
0
)
)
=LET(
c,
": ",
w,
TEXTSPLIT(
CONCAT(
REGEXREPLACE(
C2:C8,
"^|, ",
", "&A2:A8&c&B2:B8&c
)
),
c,
", ",
1
),
PIVOTBY(
TAKE(
IFERROR(
--w,
w
),
,
2
),
INDEX(
w,
,
3
),
--DROP(
w,
,
3
),
MAX,
,
0,
-2,
0
)
)Excel solution 2 for Spread Yearly Rows to Columns, proposed by 🇰🇷 Taeyong Shin:
=LET(
t,
TEXTSPLIT(
TEXTAFTER(
", "&C2:C8,
", ",
SEQUENCE(
,
5
)
),
", "
),
F,
LAMBDA(
x,
TOCOL(
IFS(
t>"",
x
),
2
)
),
fn,
LAMBDA(
p,
REGEXEXTRACT(
t,
p,
,
1
)
),
PIVOTBY(
HSTACK(
F(
A2:A8
),
F(
B2:B8
)
),
F(
fn(
"[a-z]+"
)
),
F(
--fn(
"d+"
)
),
SUM,
,
0,
-2,
0
)
)Excel solution 3 for Spread Yearly Rows to Columns, proposed by Julian Poeltl:
=LET(
T,
A2:C8,
SF,
LAMBDA(
A,
SCAN(
,
A,
LAMBDA(
A,
B,
IF(
ISERROR(
B
),
A,
B
)
)
)
),
D,
DROP(
REDUCE(
0,
SEQUENCE(
ROWS(
T
)
),
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
TAKE(
CHOOSEROWS(
T,
B
),
,
2
),
TEXTSPLIT(
INDEX(
T,
B,
3
),
": ",
", "
)
)
)
)
),
1
),
U,
TOROW(
UNIQUE(
SORT(
CHOOSECOLS(
D,
3
)
)
)
),
B,
BYROW(
HSTACK(
SF(
TAKE(
D,
,
1
)
),
SF(
CHOOSECOLS(
D,
2
)
),
SF(
CHOOSECOLS(
D,
3
)
)
),
LAMBDA(
A,
CONCAT(
A
)
)
),
VSTACK(
HSTACK(
A1:B1,
U
),
HSTACK(
TAKE(
T,
,
2
),
XLOOKUP(
TAKE(
T,
,
1
)&CHOOSECOLS(
T,
2
)&U,
B,
TAKE(
D,
,
-1
),
""
)
)
)
)Excel solution 4 for Spread Yearly Rows to Columns, proposed by Oscar Mendez Roca Farell:
=LET(
c,
C2:C8,
E,
TEXTSPLIT,
F,
LAMBDA(
i,
TOCOL(
REPT(
i,
1^-E(
CONCAT(
c&"|"
),
{":",
", "},
"|",
1
)
),
2
)
),
m,
E(
CONCAT(
c&", "
),
":",
", ",
1
),
PIVOTBY(
HSTACK(
F(
A2:A8
),
--F(
B2:B8
)
),
TAKE(
m,
,
1
),
--DROP(
m,
,
1
),
SINGLE,
,
0,
-2,
0
)
)Excel solution 5 for Spread Yearly Rows to Columns, proposed by Duy Tùng:
=LET(
v,
":",
u,
", ",
a,
TEXTSPLIT(
TEXTJOIN(
u,
,
SUBSTITUTE(
u&C2:C8,
u,
u&A2:A8&v&B2:B8&v
)
),
v,
u,
1
),
b,
IFERROR(
--a,
a
),
PIVOTBY(
TAKE(
b,
,
2
),
INDEX(
b,
,
3
),
TAKE(
b,
,
-1
),
SUM,
,
0,
-2,
0
)
)
=LET(
c,
C2:C8,
d,
TOROW(
SORT(
UNIQUE(
TAKE(
TEXTSPLIT(
ARRAYTOTEXT(
c
),
":",
", "
),
,
1
)
)
)
),
HSTACK(
A1:B8,
REDUCE(
d,
C2:C8,
LAMBDA(
x,
v,
IFNA(
VSTACK(
x,
--VLOOKUP(
d,
TEXTSPLIT(
v,
":",
", "
),
2,
)
),
""
)
)
)
)
)Excel solution 6 for Spread Yearly Rows to Columns, proposed by Sunny Baggu:
=LET(
_h,
TOROW(
SORT(
UNIQUE(
TAKE(
TEXTSPLIT(
ARRAYTOTEXT(
C2:C8
),
": ",
", "
),
,
1
)
)
)
),
_v,
DROP(
REDUCE(
"",
C2:C8,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_a,
TEXTSPLIT(
y,
": ",
", "
),
XLOOKUP(
_h,
TAKE(
_a,
,
1
),
TAKE(
_a,
,
-1
),
0
)
)
)
)
),
1
),
_r,
VSTACK(
HSTACK(
A1:B1,
_h
),
SORT(
HSTACK(
A2:B8,
_v
)
)
),
IF(
_r <> 0,
_r,
""
)
)Excel solution 7 for Spread Yearly Rows to Columns, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
", ",
b,
": ",
C,
CHOOSECOLS,
d,
TEXTSPLIT(
TEXTJOIN(
a,
,
SUBSTITUTE(
a&C2:C8,
a,
a&A2:A8&b&B2:B8&b
)
),
b,
a,
1
),
PIVOTBY(
HSTACK(
C(
d,
1
),
--C(
d,
2
)
),
C(
d,
3
),
--C(
d,
4
),
SUM,
,
0,
-2,
0
)
)Excel solution 8 for Spread Yearly Rows to Columns, proposed by Md. Zohurul Islam:
=LET(
p,
A2:B8,
q,
TRIM(
C2:C8
),
r,
HSTACK(
"Bread",
"Coke",
"Milk",
"Rice"
),
s,
DROP(
REDUCE(
"",
q,
LAMBDA(
x,
y,
LET(
a,
TRANSPOSE(
TEXTSPLIT(
y,
", "
)
),
b,
TEXTBEFORE(
a,
": "
),
c,
0+TEXTAFTER(
a,
": "
),
d,
XLOOKUP(
r,
b,
c,
""
),
e,
VSTACK(
x,
d
),
e
)
)
),
1
),
f,
SORT(
HSTACK(
p,
s
),
1
),
hdr,
HSTACK(
"Supplier",
"Date",
r
),
g,
VSTACK(
hdr,
f
),
g
)Excel solution 9 for Spread Yearly Rows to Columns, proposed by Hamidi Hamid:
=LET(
ad,
A2:A8,
cd,
C2:C8,
y,
DROP(
REDUCE(
0,
cd,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
": ",
", "
)
)
)
),
1
),
x,
TOCOL(
IF(
IFERROR(
DROP(
REDUCE(
0,
ad&"-"&cd,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
", ",
)
)
)
),
1
),
""
)<>"",
ad&"-"&B2:B8,
1/0
),
3
),
xu,
TEXTBEFORE(
x,
"-",
),
xd,
TEXTAFTER(
x,
"-",
)*1,
w,
PIVOTBY(
HSTACK(
xu,
xd
),
TAKE(
y,
,
1
),
TAKE(
y,
,
-1
)*1,
SUM,
0,
0,
1,
0
),
ww,
SORT(
w,
{1,
2},
{1,
-1}
),
ww
)Excel solution 10 for Spread Yearly Rows to Columns, proposed by Hamidi Hamid:
=LET(
ad,
A2:A8,
cd,
C2:C8,
y,
DROP(
REDUCE(
0,
cd,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
": ",
", "
)
)
)
),
1
),
x,
TOCOL(
IF(
IFERROR(
DROP(
REDUCE(
0,
ad&"-"&cd,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
", ",
)
)
)
),
1
),
""
)<>"",
ad&"-"&B2:B8,
1/0
),
3
),
xu,
TEXTBEFORE(
x,
"-",
),
xd,
TEXTAFTER(
x,
"-",
)*1,
w,
PIVOTBY(
HSTACK(
xu,
xd
),
TAKE(
y,
,
1
),
TAKE(
y,
,
-1
),
SINGLE,
0,
0,
1,
0
),
ww,
SORT(
w,
{1,
2},
{1,
-1}
),
ww
)Excel solution 11 for Spread Yearly Rows to Columns, proposed by Asheesh Pahwa:
=LET(
s,
A2:A8,
d,
B2:B8,
r,
DROP(
REDUCE(
"",
SEQUENCE(
7
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
i,
INDEX(
A2:C8,
y,
),
t,
TEXTSPLIT(
TAKE(
i,
,
-1
),
": ",
", "
),
_t,
TAKE(
t,
,
1
),
HSTACK(
TAKE(
i,
,
1
)&CHOOSECOLS(
i,
2
)&"-"&_t,
--TAKE(
t,
,
-1
)
)
)
)
)
),
1
),
_t,
TAKE(
r,
,
1
),
t,
TOROW(
SORT(
UNIQUE(
TEXTAFTER(
_t,
"-"
)
)
)
),
c,
s&d&"-"&t,
h,
HSTACK(
s,
d,
XLOOKUP(
c,
_t,
TAKE(
r,
,
-1
),
""
)
),
SORTBY(
h,
TAKE(
h,
,
1
),
1
)
)Excel solution 12 for Spread Yearly Rows to Columns, proposed by ferhat CK:
=LET(
v,
HSTACK,
h,
VSTACK,
q,
DROP(
REDUCE(
0,
C2:C8,
LAMBDA(
x,
y,
h(
x,
LET(
a,
TEXTSPLIT(
y,
,
", "
),
b,
SEQUENCE(
ROWS(
a
)
)/0,
v(
IFERROR(
b,
OFFSET(
y,
0,
-2
)
),
IFERROR(
b,
OFFSET(
& y,
0,
-1
)
),
TEXTSPLIT(
a,
": "
),
--TEXTAFTER(
a,
": "
)
)
)
)
)
),
1
),
c,
CHOOSECOLS,
d,
PIVOTBY(
c(
q,
1
)&c(
q,
2
),
c(
q,
3
),
c(
q,
4
),
MAX,
,
0,
,
0
),
e,
h(
A1:B1,
DROP(
v(
LEFT(
c(
d,
1
)
),
--RIGHT(
c(
d,
1
),
5
)
),
1
)
),
f,
v(
e,
DROP(
d,
,
1
)
),
g,
SORTBY(
f,
c(
f,
1
),
1,
c(
f,
2
),
-1
),
h(
TAKE(
g,
-1
),
DROP(
g,
-1
)
)
)Excel solution 13 for Spread Yearly Rows to Columns, proposed by Jaroslaw Kujawa:
=SORT(
LET(
y;
DROP(
REDUCE(
"";
C2:C8;
LAMBDA(
a;
x;
LET(
d;
TEXTSPLIT(
x;
":";
", "
);
s;
REPT(
OFFSET(
x;
0;
-2
)&";"&OFFSET(
x;
0;
-1
)&"|";
ROWS(
d
)
);
VSTACK(
a;
HSTACK(
TEXTSPLIT(
LEFT(
s;
LEN(
s
)-1
);
";";
"|"
);
d
)
)
)
)
);
1
);
PIVOTBY(
TAKE(
y;
;
2
);
CHOOSECOLS(
y;
3
);
1*TAKE(
y;
;
-1
);
SUM;
;
0;
;
0
)
);
{1;
2};
{1;
-1}
)Excel solution 14 for Spread Yearly Rows to Columns, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(h,
{":",
","},
VSTACK(HSTACK(
" ",
" ",
TOROW(
SORT(
UNIQUE(
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
C2:C8,
LAMBDA(
a,
TEXTJOIN(
",",
,
LET(
y,
TEXTSPLIT(
a,
h
),
LET(
x,
TOCOL(
IF(
ISERR(
VALUE(
y
)
),
y,
""
)
),
FILTER(
x,
x<>""
)
)
)
)
)
)
),
,
","
)
)
)
)
)
),
LET(b,
TEXT(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
UNIQUE(
A2:A8
),
LAMBDA(
i,
TEXTJOIN(
",",
,
LET(
m,
LET(
c,
MAP(
A2:A8,
B2:B8,
LAMBDA(
p,
o,
XLOOKUP(
i,
p,
o
)
)
),
IF(
NOT(
ISNA(
c
)
),
c,
""
)
),
FILTER(
m,
m<>""
)
)
)
)
)
),
,
","
),
"dd.mm.yyyy"
),
a,
LET(
n,
TEXTSPLIT(
TEXTJOIN(
"",
,
MAP(
UNIQUE(
A2:A8
),
LAMBDA(
d,
REPT(
d&",",
COUNTA(
LET(
m,
LET(
c,
MAP(
A2:A8,
B2:B8,
LAMBDA(
p,
o,
XLOOKUP(
d,
p,
o
)
)
),
IF(
NOT(
ISNA(
c
)
),
c,
""
)
),
FILTER(
m,
m<>""
)
)
)
)
)
)
),
,
","
),
FILTER(
n,
n<>""
)
),
HSTACK(a,
b,
DROP(TEXTSPLIT(TEXTJOIN(,
,
MAP(a,
b,
LAMBDA(y,
z,
TEXTJOIN(",",
,
IFERROR(BYCOL(TOROW(
SORT(
UNIQUE(
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
C2:C8,
LAMBDA(
a,
TEXTJOIN(
",",
,
LET(
y,
TEXTSPLIT(
a,
h
),
LET(
x,
TOCOL(
IF(
ISERR(
VALUE(
y
)
),
y,
""
)
),
FILTER(
x,
x<>""
)
)
)
)
)
)
),
,
","
)
)
)
)
),
LAMBDA(x,
FILTER(TEXTSPLIT(
TEXTJOIN(
",",
,
BYROW(
C2:C8,
LAMBDA(
y,
TEXTJOIN(
",",
,
LET(
x,
TEXTSPLIT(
y,
h
),
LET(
i,
TOCOL(
IF(
ISNUMBER(
VALUE(
x
)
),
VALUE(
x
),
""
)
),
FILTER(
i,
i<>""
)
)
)
)
)
)
),
,
","
),
((x=TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
,
BYROW(
C2:C8,
LAMBDA(
a,
LET(
q,
TEXTSPLIT(
a,
h
),
TEXTJOIN(
",",
,
TRIM(
LET(
j,
TOCOL(
IF(
ISERR(
VALUE(
q
)
),
q,
""
)
),
FILTER(
j,
j<>""
)
)
)
)
)
)
)
),
,
","
)
))Excel solution 15 for Spread Yearly Rows to Columns, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
A2:A8,
C2:C8,
LAMBDA(
m,
n,
TEXTJOIN(
",",
,
LET(
c,
TEXTSPLIT(
n,
h
),
TEXTSPLIT(
REPT(
m&",",
COUNTA(
TRIM(
LET(
j,
TOCOL(
IF(
ISERR(
VALUE(
c
)
),
c,
""
)
),
FILTER(
j,
j<>""
)
)
)
)
),
,
",",
TRUE
)
)
)
)
)
),
,
","
))*(z=TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
B2:B8,
C2:C8,
LAMBDA(
w,
e,
TEXTJOIN(
",",
,
LET(
v,
TEXTSPLIT(
e,
h
),
TEXT(
TEXTSPLIT(
REPT(
w&",",
COUNTA(
TRIM(
LET(
j,
TOCOL(
IF(
ISERR(
VALUE(
v
)
),
v,
""
)
),
FILTER(
j,
j<>""
)
)
)
)
),
,
",",
& TRUE
),
"dd.mm.yyyy"
)
)
)
)
)
),
,
","
)))>0))),
" "))&"/"))),
",",
"/"),
-1)))))Excel solution 16 for Spread Yearly Rows to Columns, proposed by Imam Hambali:
=LET(
i,
C2:C8,
li,
LEN(
i
)-LEN(
SUBSTITUTE(
i,
",",
""
)
)+1,
l,
LAMBDA(
x,
TOCOL(
IF(
SEQUENCE(
,
MAX(
li
)
)<=li,
x,
NA()
),
3
)
),
is,
TEXTSPLIT(
TEXTJOIN(
",",
1,
i
),
":",
","
),
isf,
IF(
ISERROR(
is*1
),
TRIM(
is
),
is*1
),
pv,
PIVOTBY(
HSTACK(
l(
A2:A8
),
l(
B2:B8
)
),
TAKE(
isf,
,
1
),
TAKE(
isf,
,
-1
),
SUM,
0,
0,
{1,
-2},
0
),
HSTACK(
VSTACK(
{"Supplier",
"Date"},
DROP(
TAKE(
pv,
,
2
),
1
)
),
DROP(
pv,
,
2
)
)
)Excel solution 17 for Spread Yearly Rows to Columns, proposed by Philippe Brillault:
=LET(cc,
CHOOSECOLS,
HDR,
E1 : J1,
h,
LEFT(
DROP(
HDR,
,
2
),
1
),
tt,
SORT(
_T,
1
),
a,
"(D+)(D+) ‘,b,’ [,]*(D*)(D*) ”,GENL,LAMBDA(t,c,LET(v,WRAPROWS(REGEXEXTRACT(c,a&REPT(b,3),2),2), HSTACK(INDEX(t, MATCH(c,cc(t,3),0),{1,2}),IFERROR(INDEX(cc(v,2),FIND(h,CONCAT(LEFT(cc(v,1),1)))),0)))),REDUCE(HDR,cc(tt,3),LAMBDA(aq,c,VSTACK(aq,GENL(tt,c)))))Solving the challenge of Spread Yearly Rows to Columns with Python
Python solution 1 for Spread Yearly Rows to Columns, proposed by Konrad Gryczan, PhD:
Not equal because of mistake in source worksheet.
import pandas as pd
path = "PQ_Challenge_240.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=8)
test = pd.read_excel(path, usecols="E:J", nrows=8)
input = input.assign(Items=input['Items'].str.split(', ')).explode('Items')
input[['Item', 'Quantity']] = input.pop('Items').str.split(': ', expand=True)
input['Quantity'] = pd.to_numeric(input['Quantity'])
result = input.pivot_table(index=['Supplier', 'Date'], columns='Item', values='Quantity', fill_value=0).reset_index()
result = result[['Supplier', 'Date', 'Bread', 'Coke', 'Milk', 'Rice']].sort_values(by=['Supplier', 'Date'], ascending=[True, False])
# Almost equal. Misgtake in source file.
Python solution 2 for Spread Yearly Rows to Columns, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_240.xlsx'
df = pd.read_excel(file_path, usecols='A:C')
# Perform data manipulation
df['Items'] = df['Items'].str.split(', ')
df = df.explode(column='Items')
df[['Item', 'Value']] = df['Items'].str.split(': ', expand=True)
df = (
df
.pivot(columns='Item', index=['Supplier', 'Date'], values='Value')
.fillna('')
.rename_axis('', axis=1)
.reset_index()
.sort_values(by=['Supplier', 'Date'], ascending=[True, False])
)
df
Solving the challenge of Spread Yearly Rows to Columns with Python in Excel
Python in Excel solution 1 for Spread Yearly Rows to Columns, proposed by Alejandro Campos:
df = xl("A1:C8", headers=True)
df['Items'] = df['Items'].apply(lambda s: {k.strip(): int(v) for k, v in (
item.split(':') for item in s.split(','))})
result_df = pd.concat([df.drop(columns='Items'), df['Items'].apply(lambda x: pd.Series(
{k: x.get(k, "") for k in ['Bread', 'Coke', 'Milk', 'Rice']}))], axis=1)
result_df.sort_values(by=["Supplier", "Date"], ascending=[True, False], inplace=True)
result_df.reset_index(drop=True, inplace=True)
result_df
Python in Excel solution 2 for Spread Yearly Rows to Columns, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:C8", headers=True)
data["Items"] = data["Items"].str.split(", ")
data = data.explode("Items").reset_index(drop=True)
data[["Items", "Value"]] = data["Items"].str.split(": ", expand=True)
data["Value"] = data["Value"].astype(int)
Pivot = data.pivot_table(
columns="Items", values="Value", index=["Supplier", "Date"], fill_value=""
)
result = Pivot.sort_index(level=["Supplier", "Date"], ascending=[True, False])
result
Solving the challenge of Spread Yearly Rows to Columns with R
R solution 1 for Spread Yearly Rows to Columns, proposed by Konrad Gryczan, PhD:
Not equal because of mistake in source worksheet.
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_240.xlsx"
input = read_excel(path, range = "A1:C8")
test = read_excel(path, range = "E1:J8")
result = input %>%
separate_rows(Items, sep = ", ") %>%
separate(Items, into = c("Item", "Quantity"), sep = ": ") %>%
mutate(Quantity = as.numeric(Quantity)) %>%
pivot_wider(names_from = Item, values_from = Quantity) %>%
select(Supplier, Date, Bread, Coke, Milk, Rice) %>%
arrange(Supplier, desc(Date))
all.equal(result, test, check.attributes = FALSE)
R solution 2 for Spread Yearly Rows to Columns, proposed by Abdallah Ally:
library(readxl)
library(tidyr)
library(dplyr)
file_path <- 'PQ_Challenge_240.xlsx'
df <- read_excel(file_path, range = cell_cols('A:C'))
# Perform data manipulation
df <- df %>%
mutate(Date = as.Date(Date), Items = strsplit(Items, ', ')) %>%
unnest(Items) %>%
separate(Items, into = c('Item', 'Value'), sep = ': ') %>%
arrange(Item) %>%
pivot_wider(names_from = Item, values_from = Value) %>%
arrange(Supplier, desc(Date))
df