_x000D_
Python solution 1 for Table Transformation! Part 17, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-155 Table Transformation.xlsx"
input = pd.read_excel(path, usecols="C:E", skiprows=1, nrows=21)
test = pd.read_excel(path, usecols="G:I", skiprows=1, nrows=10)
test.columns = input.columns
input.columns = ["Date", "Description", "Qty"]
input["Date"] = input["Date"].ffill()
input["Description"] = input["Description"].shift(-1)
input["Qty"] = input["Qty"].shift(-2)
input.loc[(input["Description"].notna()) & (input["Qty"].isna()), "Qty"] = "-"
input.dropna(inplace=True)
input["Date"] = pd.to_datetime(input["Date"]).dt.strftime('%d-%m-%Y')Transform the question table into the result table format.
📌 Challenge Details and Links
Challenge Number: 155
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Table Transformation! Part 17 with Power Query
_x000D_
Power Query solution 1 for Table Transformation! Part 17, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Combine(
Table.Group(
Source,
"Date",
{
"R",
each
let
l = Table.ToRows(_)
in
Table.FromRows(
List.TransformMany(
List.Positions(l),
each {{{Date.From(l{0}{0}), l{_}{1}, l{_ + 1}{2} ?? "-"}}, {}}{
Byte.From(l{_}{1} = null)
},
(i, _) => _
),
Table.ColumnNames(Source)
)
},
0,
(b, n) => Byte.From(n is datetime)
)[R]
)
in
S
_x000D_
_x000D_
Power Query solution 2 for Table Transformation! Part 17, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Date", Date.Type}, {"Qty", Text.Type}}
),
FillDown = Table.FillDown(Source, {"Date"}),
Group = Table.ExpandTableColumn(
Table.Group(
FillDown,
{"Date"},
{{"All", each Table.RemoveFirstN(Table.FillDown(_, {"Description"}), 1)}}
),
"All",
{"Description", "Qty"}
),
ReGrp = Table.Group(Group, {"Date", "Description"}, {{"Qty", each List.Max([Qty])}}),
Rep = Table.ReplaceValue(ReGrp, null, "-", Replacer.ReplaceValue, {"Qty"})
in
Rep
_x000D_
_x000D_
Power Query solution 3 for Table Transformation! Part 17, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
peb = Table.FillDown(Fonte,{"Date"}),
grp = Table.Group(peb, {"Date"}, {{"tab", each
let
a = Table.AddIndexColumn(_,"Ind", 1),
b = Table.AddColumn(a, "Qtd", each if a{[Ind]}[Qty] = null then "-" else a{[Ind]}[Qty] ),
c = Table.SelectRows(b, each [Description] <> null)[[Date],[Description],[Qtd]] in c }})[tab],
Personalizar1 = Table.Combine(grp)
in
Personalizar1
_x000D_
_x000D_
Power Query solution 4 for Table Transformation! Part 17, proposed by Rafael González B.:
let
Source = Question_Table,
TypeDate = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Group = Table.Group(TypeDate, "Date",
{{"Joined", each
[
Dt = _{0}[Date],
Skp = Table.Skip(_),
Spl = List.Transform(Table.Split(Skp, 2), each Table.TransformColumns(Table.Skip(Table.FillDown(_, {"Description"})), {{"Date", (x) => Dt}})),
Comb = Table.Combine(Spl)
]
[Comb]
}},
0, (x,y) => Number.From(y is date))[Joined],
Anw = Table.Combine(Group)
in
Anw
🧙🏻♂️🧙🏻♂️🧙🏻♂️
_x000D_
_x000D_
Power Query solution 5 for Table Transformation! Part 17, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.AddColumn(Table.AddIndexColumn(S,"I"),"D", each try S{[I]+1}[Description] otherwise null),
b = Table.AddColumn(a,"Q", each try S{[I]+2}[Qty] otherwise null)[[Date],[D],[Q]],
c = Table.FillDown(Table.SelectRows(b, each [D]<>null),{"Date"}),
Sol = Table.RenameColumns(c,List.Zip({Table.ColumnNames(c),Table.ColumnNames(S)}))
in
Sol
_x000D_
_x000D_
Power Query solution 6 for Table Transformation! Part 17, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Date",
{
"A",
each [
S1 = List.Skip([Description]),
S2 = List.Skip([Qty], 2),
Z = List.Zip({S1, S2}),
R = Table.FromRows(Z, {"D", "Q"})
][R]
},
0,
(x, y) => Number.From(y <> null)
),
Expand = Table.ExpandTableColumn(Group, "A", {"D", "Q"}, {"Description", "Qty"}),
Return = Table.SelectRows(Expand, each ([Description] <> null))
in
Return
_x000D_
_x000D_
Power Query solution 7 for Table Transformation! Part 17, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.ToColumns(Source),
B = List.Transform({0..List.Count(A)-1}, each List.RemoveFirstN(A{_},_)),
C = Table.SelectRows(Table.FromColumns(B, Table.ColumnNames(Source)),
each List.Distinct(Record.ToList(_))<>{null}),
Sol = Table.FillDown(C,{"Date"})
in
Sol
_x000D_
_x000D_
Power Query solution 8 for Table Transformation! Part 17, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(
A,
{"Date", "Description"},
{"Qty", each if [Qty]{1} = null then 0 else [Qty]{1}},
0,
(x, y) => Number.From(y[Date] is datetime or y[Description] <> null)
),
C = Table.RemoveRowsWithErrors(Table.FillDown(B, {"Date"}))
in
C
_x000D_
_x000D_
Power Query solution 9 for Table Transformation! Part 17, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FillDown = Table.Group(Table.FillDown(Source, {"Date"}), "Date", {"Data", each Table.Skip(_)}),
Expand = Table.ExpandTableColumn(FillDown, "Data", {"Description", "Qty"}),
Transform = List.Transform(List.Split(Table.ToRows(Expand), 2), each {_{0}{0}, _{0}{1}, _{1}{2}}),
FromRows = Table.FromRows(Transform, Table.ColumnNames(Source)),
Result = Table.TransformColumnTypes(FromRows, {"Date", type date})
in
Result
_x000D_
_x000D_
Power Query solution 10 for Table Transformation! Part 17, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
CondReplace = Table.ReplaceValue(
Source,
each [Qty],
each if [Description] = null and [Qty] = null then "-" else [Qty],
Replacer.ReplaceValue,
{"Qty"}
),
FillDown = Table.FillDown(CondReplace, {"Date"}),
FillUp = Table.FillUp(FillDown, {"Qty"}),
RemoveNulls = Table.SelectRows(FillUp, each [Description] <> null and [Description] <> ""),
DateType = Table.TransformColumnTypes(RemoveNulls, {{"Date", type date}})
in
DateType
_x000D_
_x000D_
Power Query solution 11 for Table Transformation! Part 17, proposed by Masoud Karami:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Filled Down" = Table.FillDown(Source, {"Date", "Description"}),
#"Grouped Rows" = Table.Group(
#"Filled Down",
{"Date", "Description"},
{{"Qty", each List.Sum([Qty]), type nullable number}}
),
#"Filtered Rows" = Table.SelectRows(
#"Grouped Rows",
each [Description] <> null and [Description] <> ""
)
in
#"Filtered Rows"
_x000D_
_x000D_
Power Query solution 12 for Table Transformation! Part 17, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.TransformColumnTypes(Source,{{"Date", type date},{"Qty", type text}}),
B = Table.FillDown(A,{"Date"}),
C = Table.SelectRows(Table.FromColumns({List.Skip(B[Date],1),List.RemoveLastN(B[Description],1),List.Skip(B[Qty],1)},Table.ColumnNames(Source)),each [Description]<>null),
D = Table.ReplaceValue(C,null,"-",Replacer.ReplaceValue,{"Qty"})
in
D
_x000D_
_x000D_
Power Query solution 13 for Table Transformation! Part 17, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.TransformColumnTypes(Source, {{"Date", type date}}),
B = Table.DuplicateColumn(A, "Date", "Date2"),
C = Table.FillDown(B, {"Date2"}),
D = Table.SelectRows(C, each ([Date] = null)),
E = Table.RemoveColumns(D, {"Date"}),
F = Table.FillDown(E, {"Description"}),
G = Table.Group(
F,
{"Date2", "Description"},
{
{
"Count",
each _,
type table [Date = nullable date, Description = nullable text, Qty = nullable number]
}
}
),
H = Table.AddColumn(G, "Q", each Table.SelectRows([Count], each [Qty] <> null)),
I = Table.ExpandTableColumn(H, "Q", {"Qty"}, {"Qty"}),
J = Table.RemoveColumns(I, {"Count"})
in
J
_x000D_
_x000D_
Power Query solution 14 for Table Transformation! Part 17, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
[
a = List.Accumulate(Table.ToColumns(Source){0}, {}, (s, c) => s & {c ?? List.Last(s)}),
b = List.Skip(Table.ToColumns(Source){1}, 1),
c = List.Transform(List.Skip(Table.ToColumns(Source){2}, 2), each _ ?? "-"),
t = Table.FromColumns({a, b, c}, Table.ColumnNames(Source))
][t],
each [Description] <> null and [Qty] <> ""
)
in
Sol
_x000D_
_x000D_
Power Query solution 15 for Table Transformation! Part 17, proposed by Khanh Lam chi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fill = Table.FillDown(Source, {"Date"}),
Add = Table.AddIndexColumn(Fill, "Index", 1),
Add0 = Table.AddColumn(Add, "Qty-", each Add[Qty]{[Index]}),
Filter = Table.SelectRows(Add0, each ([Description] <> null)),
RemoveCol = Table.RemoveColumns(Filter, {"Qty", "Index"}),
Replaced = Table.ReplaceValue(RemoveCol, null, 0, Replacer.ReplaceValue, {"Qty-"})
in
Replaced
_x000D_
_x000D_
Power Query solution 16 for Table Transformation! Part 17, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouping = Table.Combine(
Table.Group(
Table.FillDown(Source, {"Date"}),
{"Date"},
{
{
"Group",
each Table.SelectRows(
Table.FromColumns(
List.FirstN(Table.ToColumns(_), 2) & {List.Skip(_[Qty]) & {null}},
Table.ColumnNames(_)
),
(f) => f[Description] <> null
)
}
}
)[Group]
),
ChType = Table.TransformColumnTypes(Grouping, {{"Date", type date}, {"Qty", type number}})
in
ChType
_x000D_
_x000D_
Power Query solution 17 for Table Transformation! Part 17, proposed by Seokho MOON:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Date", type date}}
),
ColNames = Table.ColumnNames(Source),
Cols = List.Transform(Table.ToColumns(Source), each List.Skip(_, each _ = null)),
Table = Table.SelectRows(
Table.FromColumns(Cols, ColNames),
each List.NonNullCount(Record.FieldValues(_)) > 0
),
FilledDown = Table.FillDown(Table, {"Date"})
in
FilledDown
_x000D_
_x000D_
Power Query solution 18 for Table Transformation! Part 17, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.FillDown(Table.TransformColumnTypes(A,{{"Date", type date}}), {"Date"}),
C = Table.ToColumns(B),
D = List.Select(List.Accumulate(List.Positions(B[Date]), {}, (s,c)=> s & {{C{0}{c}, C{1}{c}, C{2}{c} ?? C{2}{c+1} ?? "-"}}), each _{1} <> null),
E = Table.FromRows(D, Value.Type(B))
in E
_x000D_
_x000D_
Power Query solution 19 for Table Transformation! Part 17, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0, 1),
Qty = Table.RenameColumns(
Table.RemoveColumns(
Table.AddColumn(
Index,
"Up",
each if [Index] + 1 < Table.RowCount(Source) then Source[Qty]{[Index] + 1} else null
),
{"Qty", "Index"}
),
{"Up", "Qty"}
),
#"Filled Down" = Table.FillDown(Qty, {"Date"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Description] <> null))
in
#"Filtered Rows"
_x000D_
Solving the challenge of Table Transformation! Part 17 with Excel
_x000D_
Excel solution 1 for Table Transformation! Part 17, proposed by Oscar Mendez Roca Farell:
=FILTER(
HSTACK(
SCAN(
,
C3:C22,
MAX
),
D3:D22,
E4:E23
),
D3:D22>""
)
_x000D_
_x000D_
Excel solution 2 for Table Transformation! Part 17, proposed by Julian Poeltl:
=LET(
D,
C3:C23,
S,
D3:D23,
Q,
E3:E23,
F,
FILTER(
SEQUENCE(
ROWS(
S
)
),
S<>""
),
L,
INDEX(
Q,
F+1
),
Sc,
SCAN(
0,
INDEX(
D,
F-1
),
LAMBDA(
A,
B,
IF(
B="",
A,
B
)
)
),
VSTACK(
C2:E2,
HSTACK(
Sc,
INDEX(
S,
F
),
IF(
L,
L,
"-"
)
)
)
)
_x000D_
_x000D_
Excel solution 3 for Table Transformation! Part 17, proposed by Kris Jaganah:
=LET(
a,
SCAN(
,
C3:C23,
MAX
),
b,
D3:D23,
c,
INDEX(
E3:E23,
MATCH(
a&b,
a&b,
0
)+1
),
FILTER(
HSTACK(
a,
b,
c
),
b<>0
)
)
_x000D_
_x000D_
Excel solution 4 for Table Transformation! Part 17, proposed by Imam Hambali:
=LET( de,
DROP(
D3:D23,
-1
), q,
DROP(
E3:E23,
1
), s,
SCAN(
,
DROP(
C3:C23,
-1
),
MAX
), VSTACK(
C2:E2,
FILTER(
HSTACK(
s,
de,
q
),
de<>0
)
))
_x000D_
_x000D_
Excel solution 5 for Table Transformation! Part 17, proposed by Sunny Baggu:
=LET(
_f,
SCAN(
"",
C3:C22,
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
), _a,
DROP(
HSTACK(
_f,
IF(
D3:D22 = "",
"",
D3:D22
)
),
1
), _b,
DROP(
IF(
E3:E23 = "",
"",
E3:E23
),
2
), FILTER(
HSTACK(
_a,
_b
), (TAKE(
_a,
,
-1
) <> "") + (_b <> "")
)
)
_x000D_
_x000D_
Excel solution 6 for Table Transformation! Part 17, proposed by Ankur Sharma:
=LET(
a,
SCAN(
"",
C3:C21,
LAMBDA(
i,
ar,
IF(
ar = "",
i,
ar
)
)
), b,
HSTACK(
a,
D4:D22,
E5:E23
), FILTER(
b,
CHOOSECOLS(
b,
2
) <> ""
)
)
_x000D_
_x000D_
Excel solution 7 for Table Transformation! Part 17, proposed by Asheesh Pahwa:
=LET(
dt,
C3:C23,
s,
SCAN(
0,
dt,
LAMBDA(
x,
y,
IF(
y<>"",
y,
x
)
)
),
r,
DROP(
REDUCE(
"",
UNIQUE(
s
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
d,
DROP(
FILTER(
D3:E23,
s=y
),
1
),
IFNA(
HSTACK(
y,
TAKE(
d,
,
1
),
DROP(
TAKE(
d,
,
-1
),
1
)
),
y
)
)
)
)
),
1
),
f,
FILTER(
r,
INDEX(
r,
,
2
)<>0
),
f
)
_x000D_
_x000D_
Excel solution 8 for Table Transformation! Part 17, proposed by Burhan Cesur:
=LET(
a;
IFNA(
HSTACK(
SCAN(
"";
C3:C23;
LAMBDA(
s;
v;
IF(
v="";
s;
v
)
)
);
DROP(
D3:D23;
1
);
IF(
VSTACK(
DROP(
E3:E23;
2
);
0
)=0;
"-";
VSTACK(
DROP(
E3:E23;
2
);
0
)
)
);
0
);
FILTER(
a;
INDEX(
a;
;
2
)<>0
)
)
_x000D_
_x000D_
Excel solution 9 for Table Transformation! Part 17, proposed by Edwin Tisnado:
=LET(
b,
D4:D23,
FILTER(
HSTACK(
SCAN(
,
C3:C22,
MAX
),
b,
E5:E24
),
b>0
)
)
_x000D_
_x000D_
Excel solution 10 for Table Transformation! Part 17, proposed by ferhat CK:
=LET(
a,
DROP(
C3:C23,
-2
),
b,
DROP(
DROP(
D3:D23,
1
),
-1
),
c,
DROP(
E3:E23,
2
),
FILTER(
HSTACK(
SCAN(
,
a,
MAX
),
b,
c
),
b>0
)
)
_x000D_
_x000D_
Excel solution 11 for Table Transformation! Part 17, proposed by Gabriel Pugliese:
=LET(
d,
SCAN(
,
C3:C23,
LAMBDA(
a,
v,
IF(
v,
v,
a
)
)
), f,
D3:D23, q,
DROP(
E3:E23,
1
), FILTER(
HSTACK(
d,
f,
IF(
q=0,
"-",
q
)
),
f>0
)
)
_x000D_
_x000D_
Excel solution 12 for Table Transformation! Part 17, proposed by Hamidi Hamid:
=LET(
x,
SCAN(
,
C3:C23,
MAX
),
y,
D3:D23,
z,
DROP(
E3:E23,
1
),
r,
IF(
z=0,
"-",
0
),
FILTER(
HSTACK(
x,
y,
IF(
z=0,
"-",
z
)
),
y>0
)
)
_x000D_
_x000D_
Excel solution 13 for Table Transformation! Part 17, proposed by Hussein SATOUR:
=LET(
a,
C3:E23,
I,
INDEX,
d,
I(
a,
,
2
),
FILTER(
HSTACK(
SCAN(
,
I(
a,
,
1
),
MAX
),
d,
DROP(
I(
a,
,
3
),
1
)
),
d<>0
)
)
_x000D_
_x000D_
Excel solution 14 for Table Transformation! Part 17, proposed by Khanh Lam chi:
=LET(
a,
D4:D23,
FILTER(
HSTACK(
SCAN(
"",
C3:C22,
LAMBDA(
x,
y,
IF(
y<>"",
y,
x
)
)
),
a,
E5:E24
),
a<>0
)
)
_x000D_
_x000D_
Excel solution 15 for Table Transformation! Part 17, proposed by Peter Bartholomew:
=LET( FILLDOWNλ,
LAMBDA(
a,
b,
IF(
b <> "",
b,
a
)
), qty,
DROP(
quantity,
1
), dt,
SCAN(
0,
date,
FILLDOWNλ
), FILTER(
HSTACK(
dt,
descr,
qty
),
ISTEXT(
descr
)
))
_x000D_
_x000D_
Excel solution 16 for Table Transformation! Part 17, proposed by Pieter de B.:
=WRAPROWS(
TOCOL(
IFS(
LEN(
D4:D22
),
HSTACK(
SCAN(
,
C3:C21,
LAMBDA(
a,
b,
IF(
b,
b,
a
)
)
),
D4:D22,
E5:E23
)
),
2
),
3
)
_x000D_
_x000D_
Excel solution 17 for Table Transformation! Part 17, proposed by Rick Rothstein:
=LET(
g,
HSTACK(
SCAN(
,
C3:C23,
LAMBDA(
a,
x,
IF(
x="",
a,
x
)
)
),
DROP(
D3:D23,
1
),
DROP(
IF(
E3:E23="",
"-",
E3:E23
),
2
)
),
VSTACK(
C2:E2,
FILTER(
g,
ISTEXT(
CHOOSECOLS(
g,
2
)
)
)
)
)
_x000D_
Solving the challenge of Table Transformation! Part 17 with Python
_x000D_
Python solution 1 for Table Transformation! Part 17, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-155 Table Transformation.xlsx"
input = pd.read_excel(path, usecols="C:E", skiprows=1, nrows=21)
test = pd.read_excel(path, usecols="G:I", skiprows=1, nrows=10)
test.columns = input.columns
input.columns = ["Date", "Description", "Qty"]
input["Date"] = input["Date"].ffill()
input["Description"] = input["Description"].shift(-1)
input["Qty"] = input["Qty"].shift(-2)
input.loc[(input["Description"].notna()) & (input["Qty"].isna()), "Qty"] = "-"
input.dropna(inplace=True)
input["Date"] = pd.to_datetime(input["Date"]).dt.strftime('%d-%m-%Y')