Transform the question structure into the result structure.
📌 Challenge Details and Links
Challenge Number: 157
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Table Transformation! Part 18 with Power Query
_x000D_Power Query solution 1 for Table Transformation! Part 18, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Column 1],
S = Table.FromRows(
List.TransformMany(
List.Split(Source, 3),
each
let
s = each Text.Split(_, ",")
in
List.Zip({s(_{1}), s(Text.From(_{2}))}),
(i, _) => {i{0}, _{0}, Number.From(_{1})}
),
{"Date", "Product", "Quantity"}
)
in
SPower Query solution 2 for Table Transformation! Part 18, proposed by Brian Julius:
let
Source = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], "Mod"),
AddMod = Table.TransformColumns(Source, {{"Mod", each Number.Mod(_, 3), type number}}),
AddDate = Table.FillDown(
Table.AddColumn(
AddMod,
"Date",
each if Value.Is([Column 1], DateTime.Type) then [Column 1] else null
),
{"Date"}
),
Filtter = Table.SelectRows(AddDate, each ([Mod] <> 0)),
Pivot = Table.TransformColumnTypes(
Table.Pivot(
Table.TransformColumnTypes(Filtter, {{"Mod", type text}}, "en-US"),
List.Distinct(Table.TransformColumnTypes(Filtter, {{"Mod", type text}}, "en-US")[Mod]),
"Mod",
"Column 1"
),
{"2", Text.Type}
),
Split1 = Table.ExpandListColumn(
Table.TransformColumns(Pivot, {{"1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}}),
"1"
),
Split2 = Table.ExpandListColumn(
Table.TransformColumns(Pivot, {{"2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}}),
"2"
),
ToCols = List.FirstN(Table.ToColumns(Split1), 2) & {List.Last(Table.ToColumns(Split2))},
FromCols = Table.TransformColumnTypes(
Table.FromColumns(ToCols, {"Date", "Product", "Quantity"}),
{"Date", Date.Type}
)
in
FromColsPower Query solution 3 for Table Transformation! Part 18, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(
Fonte,
"Column 1",
{
{
"tab",
each Table.FromColumns(
List.Transform(_[Column 1], (x) => Text.Split(Text.From(x), ",")),
{"Date", "Product", "Quantity"}
)
}
},
1,
(a, b) => Number.From(b is datetime)
)[tab],
cmb = Table.Combine(grp),
rst = Table.FillDown(cmb, {"Date"})
in
rstPower Query solution 4 for Table Transformation! Part 18, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.Combine(List.Transform(Table.Split(S,3),Table.Transpose)),
b = Table.TransformColumnTypes(a,{"Column3", type text}),
c = Table.TransformColumns(b,{{"Column2", each Text.Split(_,",")},{"Column3", each Text.Split(_,",")}}),
d = Table.AddColumn(c,"M", each Table.FromRows(List.Zip({[Column2],[Column3]})))[[Column1],[M]],
e = Table.ExpandTableColumn(d,"M",{"Column1","Column2"},{"Column1.1","Column2"}),
Sol = Table.RenameColumns(e,List.Zip({Table.ColumnNames(e),{"Date","Product","Quantity"}}))
in
SolPower Query solution 5 for Table Transformation! Part 18, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl = Table.Combine(Table.Group(Source, "Column 1", {"A", each
let
a = Table.ToRows(_),
b = List.Transform(a, each try Text.Split(_{0}, ",") otherwise {_{0}}),
c = Table.FromColumns(b, {"Date", "Product", "Quantity"}),
d = Table.FillDown(c,{"Date"})
in d}, 0, (x,y)=> Number.From(y is datetime))[A]),
Type = Table.TransformColumnTypes(Tbl,{{"Quantity", Int64.Type}})
in
TypePower Query solution 6 for Table Transformation! Part 18, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"VYzBDcAgDAN34W0pIVA1fRbGiNh/DVr8KP04kn25iGQqp5haTQOR7pVZ1znEv6WjgavDYOTKTjR2TBe9Nu3z3OlERSEiWX8E21c9Jg==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Column 1" = _t]
),
GetTab = Table.Combine(
List.Transform(
List.Split(Source[Column 1], 3),
each Table.FromRows({_}, {"Date", "Product", "Quantity"})
)
),
GetLists = List.Accumulate(
List.Skip(Table.ColumnNames(GetTab)),
GetTab,
(s, c) => Table.TransformColumns(s, {c, each Text.Split(_, ",")})
),
AddRec = Table.AddColumn(GetLists, "NT", each Table.FromColumns(List.Skip(Record.ToList(_))))[
[Date],
[NT]
],
Result = Table.ExpandTableColumn(AddRec, "NT", {"Column1", "Column2"}, {"Product", "Quantity"})
in
ResultPower Query solution 7 for Table Transformation! Part 18, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.FromColumns(
{List.TransformMany(A[Column 1], each Text.Split(Text.From(_), ","), (x, y) => y)}
),
C = Table.Combine(
Table.Group(
B,
"Column1",
{
"All",
(v) =>
Table.FromList(
List.Zip(List.Split(List.Skip(v[Column1]), (Table.RowCount(v) - 1) / 2)),
each {v[Column1]{0}, _{0}, _{1}},
{"Date", "Product", "Quantity"}
)
},
0,
(x, y) => Number.From((try DateTime.FromText(y) otherwise null) is datetime)
)[All]
)
in
CPower Query solution 8 for Table Transformation! Part 18, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook()[Content]{0}[Column 1],
B = List.TransformMany(A, each Text.Split(Text.From(_), ","), (x, y) => y),
C = List.Accumulate(
B,
{},
(x, y) => x & ({try if DateTime.FromText(y) is datetime then y else "" otherwise List.Last(x)})
),
D = List.RemoveNulls(
List.Transform(
List.Positions(C),
each try (if (B{_} = C{_}) or (Number.From(B{_}) > 0) then null else 0) otherwise C{_}
)
),
E = List.Select(B, each try Number.From(_) is number otherwise null),
F = List.Difference(B, D & E),
G = Table.FromColumns({D, F, E}, {"Date", "Product", "Quantity"})
in
GPower Query solution 9 for Table Transformation! Part 18, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = List.Transform(
List.Split(Source[Column 1], 3),
each List.Zip(List.Transform(_, (x) => Text.Split(Text.From(x), ",")))
),
FromRows = Table.FromRows(List.Combine(Transform), {"Date", "Product", "Quantity"}),
Result = Table.TransformColumns(
Table.FillDown(FromRows, {"Date"}),
{"Date", each Date.From(DateTime.FromText(_)), type date}
)
in
ResultPower Query solution 10 for Table Transformation! Part 18, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.TransformColumnTypes(S, {{"Column 1", type text}}),
A = Table.FromColumns({List.Split(T[Column 1], 3)}, {"Data"}),
B = Table.AddColumn(
A,
"T",
each Table.FromColumns(
{
List.Repeat({[Data]{0}}, List.Count(Text.Split([Data]{1}, ","))),
Text.Split([Data]{1}, ","),
Text.Split([Data]{2}, ",")
},
{"Date", "Product", "Quantity"}
)
),
C = Table.Combine(B[T]),
D = Table.TransformColumnTypes(C, {{"Date", type datetime}}),
E = Table.TransformColumnTypes(D, {{"Date", type date}})
in
EPower Query solution 11 for Table Transformation! Part 18, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Question"]}[Content],
A = Table.Split(Source, 3),
FxC = (t as table) =>
let
C = Table.SplitColumn(
Table.TransformColumnTypes(t, {{"Column 1", type text}}, "en-IN"),
"Column 1",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
3,
null,
MissingField.Ignore
),
D = Table.FillDown(Table.Transpose(C), {"Column1"})
in
D,
E = Table.Combine(List.Transform(A, each FxC(_))),
F = Table.SelectRows(E, each ([Column2] <> null))
in
FPower Query solution 12 for Table Transformation! Part 18, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
lst = List.Transform(Source[Column 1], each try Text.Split(_, ",") otherwise {_}),
Tables = List.Transform(
List.Split(lst, 3),
each Table.FromColumns(_, {"Date", "Product", "Quantity"})
),
Res = Table.TransformColumnTypes(
Table.FillDown(Table.Combine(Tables), {"Date"}),
{{"Date", type date}, {"Product", type text}, {"Quantity", type number}}
)
in
ResPower Query solution 13 for Table Transformation! Part 18, proposed by Vida Vaitkunaite:
let
Source = List.Transform(
Table.Split(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 3),
(x) =>
[
TypeText = Table.TransformColumnTypes(x, {{"Column 1", type text}}),
MaxLetters = List.Max(
Table.AddColumn(
x,
"Custom",
each try Text.Length(Text.Replace([Column 1], ",", "")) otherwise null
)[Custom]
),
Split = Table.SplitColumn(
TypeText,
"Column 1",
Splitter.SplitTextByDelimiter(","),
MaxLetters
),
Transpose = Table.Transpose(Split, {"Date", "Product", "Quantity"}),
FillDown = Table.FillDown(Transpose, {"Date"})
][FillDown]
),
Result = Table.Combine(Source),
ParseDate = Table.TransformColumns(
Result,
{{"Date", each Date.From(DateTimeZone.From(_)), type date}}
)
in
ParseDateSolving the challenge of Table Transformation! Part 18 with Excel
_x000D_Excel solution 1 for Table Transformation! Part 18, proposed by Oscar Mendez Roca Farell:
=LET(
R,
FILTER,
H,
HSTACK,
F,
LAMBDA(
i,
TEXTSPLIT(
CONCAT(
CHOOSECOLS(
WRAPROWS(
C3:C17,
3
),
H(
i,
3
)
)&","
),
,
",",
1
)
),
b,
F(
2
),
H(
R(
SCAN(
0,
F(
1
),
MAX
),
--F(
1
)<11
),
R(
b,
ISERR(
-b
)
),
-TOCOL(
-b,
2
)
)
)Excel solution 2 for Table Transformation! Part 18, proposed by Julian Poeltl:
=LET(
C,
C3:C17,
L,
LAMBDA(
T,
H,
REDUCE(
H,
T,
LAMBDA(
A,
B,
VSTACK(
A,
TEXTSPLIT(
B,
,
","
)
)
)
)
),
CR,
LAMBDA(
S,
CHOOSEROWS(
C,
SEQUENCE(
ROWS(
C
)/3,
,
S,
3
)
)
),
CC,
CONCAT(
REPT(
CR(
1
)&",",
LEN(
CR(
2
)
)-LEN(
SUBSTITUTE(
CR(
2
),
",",
""
)
)+1
)
),
CCC,
LEFT(
CC,
LEN(
CC
)-1
),
H,
HSTACK(
L(
CCC,
"Date"
),
L(
CR(
2
),
"Product"
),
L(
CR(
3
),
"Quantity"
)
),
IFERROR(
--H,
H
)
)Excel solution 3 for Table Transformation! Part 18, proposed by Kris Jaganah:
=LET(a,
C3:C17,
b,
TEXTSPLIT(
CONCAT(
a&","
),
,
",",
1
),
c,
--SCAN(
,
b,
LAMBDA(
x,
y,
IF(
LEN(
y
)>3,
y,
x
)
)
),
HSTACK(FILTER(
c,
ISERR(
-b
)
),
FILTER(
b,
ISERR(
-b
)
),
--FILTER(b,
(-ISERR(
-b
)=0)*(LEN(
b
)<3))))Excel solution 4 for Table Transformation! Part 18, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
ROWS(
C3:C17
)
), _c,
MOD(
_s,
3
), _e1,
LAMBDA(
d,
FILTER(
C3:C17,
_c = d
)
), _d,
_e1(1), _p,
_e1(2), _q,
_e1(0), REDUCE( {"Date",
"Product",
"Quantity"}, SEQUENCE(
ROWS(
_d
)
), LAMBDA(
a,
v, VSTACK(
a,
LET(
_c1,
INDEX(
_d,
v,
1
),
_c2,
INDEX(
_p,
v,
1
),
_c3,
INDEX(
_q,
v,
1
),
IFNA(
HSTACK(
_c1,
TEXTSPLIT(
_c2,
,
","
),
TEXTSPLIT(
_c3,
,
","
)
),
_c1
)
)
)
) )
)Excel solution 5 for Table Transformation! Part 18, proposed by Asheesh Pahwa:
=LET(
w,
WRAPROWS(
C3:C17,
3
),
REDUCE(
E2:G2,
SEQUENCE(
5
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
i,
INDEX(
w,
y,
),
t,
TAKE(
i,
,
1
),
IFNA(
HSTACK(
t,
DROP(
REDUCE(
"",
DROP(
i,
,
1
),
LAMBDA(
a,
v,
HSTACK(
a,
TEXTSPLIT(
v,
,
","
)
)
)
),
,
1
)
),
t
)
)
)
)
)
)Excel solution 6 for Table Transformation! Part 18, proposed by ferhat CK:
=DROP(
REDUCE(
0,
SEQUENCE(
ROWS(
C3:C17
)/3,
,
3,
3
),
LAMBDA(
x,
y,
VSTACK(
x,
IFERROR(
HSTACK(
CHOOSEROWS(
C3:C17,
y-2
),
TEXTSPLIT(
TAKE(
TAKE(
TAKE(
TAKE(
C3:C17,
y
),
-3
),
-2
),
1
),
,
","
),
TEXTSPLIT(
TAKE(
TAKE(
TAKE(
TAKE(
C3:C17,
y
),
-3
),
-2
),
-1
),
,
","
)
),
CHOOSEROWS(
C3:C17,
y-2
)
)
)
)
),
1
)Excel solution 7 for Table Transformation! Part 18, proposed by Hamidi Hamid:
=LET(
c,
C3:C17,
k,
LAMBDA(
s,
IF(
ISERROR(
s*1
),
s,
1/0
)
),
z,
SCAN(
,
c,
MAX
),
f,
LAMBDA(
gg,
REDUCE(
0,
gg,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
",",
,
)
)
)
)
),
zz,
IFERROR(
DROP(
f(
c
),
1
),
1/0
),
r,
TOCOL(
TEXTSPLIT(
TOCOL(
zz,
3
),
z,
,
1
)*1,
3
),
g,
TEXTSPLIT(
c,
r, ),
p,
IF(
g="",
0,
g
),
x,
zz,
v,
k(
x
),
q,
TOCOL(
IF(
ISERROR(
v
),
1/0,
z
),
3
),
w,
f(
c
),
u,
TOCOL(
k(
w
),
3
),
HSTACK(
q,
u,
r
)
)Excel solution 8 for Table Transformation! Part 18, proposed by Md. Zohurul Islam:
=LET(
hdr,
HSTACK(
"Date",
"Product",
"Quantity"
),
z,
C3:C17,
p,
WRAPROWS(
z,
3
),
q,
BYROW(
p,
LAMBDA(
x,
TEXTJOIN(
"/",
,
x
)
)
), s,
DROP(
REDUCE(
"",
q,
LAMBDA(
x,
y,
LET(
a,
--TEXTBEFORE(
y,
"/"
),
b,
TEXTSPLIT(
TEXTBEFORE(
TEXTAFTER(
y,
"/",
1
),
"/"
),
,
","
),
c,
--TEXTSPLIT(
TEXTAFTER(
y,
"/",
2
),
,
","
),
d,
IFNA(
HSTACK(
a,
b,
c
),
a
),
e,
VSTACK(
x,
d
),
e
)
)
),
1
),
u,
VSTACK(
hdr,
s
),
u
)Excel solution 9 for Table Transformation! Part 18, proposed by Pieter de B.:
=LET(
a,
C3:C17,
i,
LAMBDA(
j,
INDEX(
C3:C17,
SEQUENCE(
ROWS(
a
)/3,
,
j,
3
)
)
),
p,
LAMBDA(
q,
TEXTSPLIT(
TEXTAFTER(
","&i(
q
),
",",
{1,
2,
3}
),
","
)
),
c,
TOCOL,
HSTACK(
c(
IFS(
1-ISERROR(
p(
2
)
),
i(
1
)
),
2
),
c(
p(
2
),
2
),
c(
p(
3
),
2
)
)
)Excel solution 10 for Table Transformation! Part 18, proposed by red craven:
=LET(
a,
WRAPROWS(
C3:C17,
3
),
b,
REDUCE(
E2:G2,
SEQUENCE(
ROWS(
a
)
),
LAMBDA(
x,
y,
VSTACK(
x,
IFNA(
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
"|",
,
INDEX(
a,
y
)
),
",",
"|"
)
),
INDEX(
a,
y
)
)
)
)
),
IFERROR(
0+b,
b
)
)Solving the challenge of Table Transformation! Part 18 with Python
_x000D_Python solution 1 for Table Transformation! Part 18, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-157 Table Transformation.xlsx"
input = pd.read_excel(path, usecols="C", skiprows=1, nrows=15, dtype=str)
test = pd.read_excel(path, usecols="E:G", skiprows=1, nrows=10)
input_matrix = input.values.reshape(-1, 3)
result = pd.DataFrame(input_matrix, columns=["V1", "V2", "V3"])
result = result.assign(V2=result['V2'].str.split(','), V3=result['V3'].str.split(',')).explode(['V2', 'V3'])
result['V1'] = pd.to_datetime(result['V1'], errors='coerce')
result['V3'] = pd.to_numeric(result['V3'], errors='coerce').astype('Int64')
result.reset_index(drop=True, inplace=True)
result.columns = test.columns
print(all(result == test)) # TruePython solution 2 for Table Transformation! Part 18, proposed by Luan Rodrigues:
import pandas as pd
file = "CH-157 Table Transformation.xlsx"
df = pd.read_excel(file,usecols='C',skiprows=1)
df['Column 1'] = df['Column 1'].astype('str')
df['Date'] = df['Column 1'].where(df['Column 1'].str.contains('-')).ffill()
def tab(x):
a = list(x['Column 1'])[1:]
b = [i.split(',') for i in a]
c = pd.DataFrame(b).T
c.columns = ['Product','Quantity']
c['Date'] = x.name
return c
grp = df.groupby('Date', group_keys=False).apply(tab)[['Date','Product','Quantity']]
print(grp)