Transform the question structure into the result structure. If the quantity for more than one product be the same, it is repeated just one.
📌 Challenge Details and Links
Challenge Number: 177
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Table Transformation! Part 21 with Power Query
Power Query solution 1 for Table Transformation! Part 21, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(Fonte, "Column 1", {{"tab", each
let
a = Table.AddColumn(Table.Skip(_,1), "Quantity", each if [Column 1] is number then [Column 1] else null),
b = Table.FillUp(a,{"Quantity"}),
c = Table.SelectRows(b, each [Column 1] is text)
in
Table.RenameColumns(c,{{"Column 1","Product"}}) }},0,(a,b)=> Number.From(b is datetime) ),
res = Table.ExpandTableColumn(grp, "tab", Table.ColumnNames(grp[tab]{0}))
in
resPower Query solution 2 for Table Transformation! Part 21, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Date = Table.AddColumn(Source, "Date", each if [Column 1] is datetime then [Column 1] else null),
FD = Table.FillDown(Date, {"Date"}),
Prod = Table.AddColumn(FD, "Product", each if [Column 1] is text then [Column 1] else null),
Quant = Table.AddColumn(Prod, "Quantity", each if [Column 1] is number then [Column 1] else null),
FU = Table.FillUp(Quant, {"Product", "Quantity"}),
Sol = Table.RemoveColumns(Table.SelectRows(FU, each [Column 1] is text), "Column 1")
in
SolPower Query solution 3 for Table Transformation! Part 21, proposed by Krzysztof Kominiak:
let
Source = Table.RenameColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Column 1", "Date"}}
),
GroupRows = Table.Group(
Source,
"Date",
{{"NT", each Table.Skip(_, 1)}},
0,
(x, y) => Number.From(Value.Is(y, type datetime))
),
AddTemp = Table.AddColumn(
GroupRows,
"temp",
each Table.FillUp(
Table.Group(
[NT],
"Date",
{{"Value", each try Table.Skip(_, 1)[Date]{0} otherwise null}},
0,
(x, y) => Number.From(Value.Is(y, type text))
),
{"Value"}
)
),
RemCols = Table.RemoveColumns(AddTemp, {"NT"}),
Result = Table.ExpandTableColumn(RemCols, "temp", {"Date", "Value"}, {"Product", "Quantity"})
in
ResultPower Query solution 4 for Table Transformation! Part 21, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"Column 1",
{
"Data",
each [
a = [Column 1],
b = List.Select(a, each _ is text),
c = List.Select(a, each _ is number),
d = List.Zip({b, List.Repeat({null}, List.Count(b) - List.Count(c)) & c}),
e = Table.FromRows(d, {"Product", "Quantity"})
][e]
},
0,
(x, y) => Byte.From(y is datetime)
),
Expand = Table.ExpandTableColumn(Group, "Data", {"Product", "Quantity"}),
Rename = Table.RenameColumns(Table.FillUp(Expand, {"Quantity"}), {"Column 1", "Date"}),
Result = Table.TransformColumns(Rename, {"Date", Date.From, type date})
in
ResultPower Query solution 5 for Table Transformation! Part 21, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.RenameColumns(A, {"Column 1", "Product"}),
C = Table.AddColumn(
B,
"Date",
each if Value.Is([Product], type datetime) then [Product] else null
),
D = Table.FillDown(C, {"Date"}),
E = Table.AddColumn(
D,
"Quantity",
each if Value.Is([Product], type number) then [Product] else null
),
F = Table.FillUp(E, {"Quantity"}),
G = Table.SelectRows(F, each Value.Is([Product], type text))[[Date], [Product], [Quantity]]
in
GPower Query solution 6 for Table Transformation! Part 21, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.ExpandRecordColumn(
Table.AddColumn(
Source,
"Custom",
each [
a = Text.Contains(Text.From([Column 1]), ":"),
b = if a = true then Date.From([Column 1]) else null
][[a], [b]]
),
"Custom",
{"a", "b"},
{"a", "Date"}
),
B = Table.FillDown(A, {"Date"}),
C = Table.SelectRows(B, each ([a] = false)),
D = Table.FillUp(
Table.AddColumn(C, "Custom", each try Number.From([Column 1]) otherwise null),
{"Custom"}
),
E = Table.SelectRows(D, each not ([Column 1] is number)),
F = Table.SelectColumns(E, {"Date", "Column 1", "Custom"}),
Result = Table.RenameColumns(F, {{"Column 1", "Product"}})
in
ResultPower Query solution 7 for Table Transformation! Part 21, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Group = Table.Combine(
Table.Group(
Source,
"Column 1",
{
{
"New",
each [
Lst = [Column 1],
txtlst = List.Select(Lst, each _ is text),
numlst = List.Select(Lst, each _ is number),
fin = Table.FillDown(
Table.FillUp(
Table.FromRows(
List.Zip(
{
{Lst{0}},
txtlst,
List.Repeat({null}, List.Count(txtlst) - List.Count(numlst)) & numlst
}
),
{"Date", "Product", "Qty"}
),
{"Qty"}
),
{"Date"}
)
][fin]
}
},
0,
(x, y) => Number.From(y is datetime)
)[New]
),
Result = Table.TransformColumnTypes(Group, {{"Date", type date}})
in
ResultPower Query solution 8 for Table Transformation! Part 21, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Group = Table.Combine(
Table.Group(
Source,
"Column 1",
{
{
"New",
each Table.Combine(
List.Transform(
List.Split(
List.Accumulate(
List.Skip(_[Column 1]),
{},
(s, c) =>
if List.Last(s) is text and c is text then
List.RemoveLastN(s, 1) & {List.Last(s) & ", " & c}
else
s & {c}
),
2
),
(f) =>
Table.FillDown(
Table.FromRows(
List.Zip({{[Column 1]{0}}, Text.Split(f{0}, ", "), {f{1}}}),
{"Date", "Product", "Qty"}
),
{"Date", "Qty"}
)
)
)
}
},
0,
(x, y) => Number.From(y is datetime)
)[New]
),
Result = Table.TransformColumnTypes(Group, {"Date", type date})
in
ResultPower Query solution 9 for Table Transformation! Part 21, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Column 1],
Recs = List.Accumulate(
Source,
[Date = {}, Product = {}, Quantity = {}],
(a, v) =>
if Value.Is(v, type datetime) then
[Date = a[Date] & {v}, Product = a[Product], Quantity = a[Quantity]]
else if Value.Is(v, type text) then
[
Date = a[Date]
& List.Repeat({List.Last(a[Date])}, List.Count(a[Product]) - List.Count(a[Date]) + 1),
Product = a[Product] & {v},
Quantity = a[Quantity]
]
else
[
Date = a[Date],
Product = a[Product],
Quantity = a[Quantity]
& List.Repeat({v}, List.Count(a[Product]) - List.Count(a[Quantity]))
]
),
Res = Table.FromColumns(Record.ToList(Recs), Record.FieldNames(Recs))
in
ResPower Query solution 10 for Table Transformation! Part 21, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
grup = Table.ExpandTableColumn(
Table.Group(
Source,
{"Column 1"},
{
{
"r",
each [
tr = Table.ReverseRows,
a = tr(Table.Skip(_, 1)),
b = Table.ExpandTableColumn(
tr(
Table.Group(
a,
{"Column 1"},
{"s", each tr(Table.Skip(_, 1))},
GroupKind.Local,
(x, y) => Number.From(y[Column 1] is number)
)
),
"s",
{"Column 1"},
{"Product"}
)
][b]
}
},
GroupKind.Local,
(x, y) => Number.From(y[Column 1] is datetime)
),
"r",
{"Column 1", "Product"},
{"Quantity", "Product"}
),
RRColumns = Table.SelectColumns(
Table.RenameColumns(grup, {{"Column 1", "Date"}}),
{"Date", "Product", "Quantity"}
)
in
RRColumnsPower Query solution 11 for Table Transformation! Part 21, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Column 1"}, {{"All", each let
a = Table.RemoveFirstN(_, 1),
b = Table.AddColumn(a, "Type", each if Type.Is(Value.Type([Column 1]), type text) then "Product" else "Quantity"),
c = Table.AddIndexColumn(b, "Index"),
d = Table.Pivot(c, List.Sort(List.Distinct(c[Type])), "Type", "Column 1" ),
e = Table.SelectRows(Table.FillUp(d,{"Quantity"}), each ([Product] <> null))
in e
}}, 0,
(x,y)=> Number.From( Type.Is(Value.Type(x[Column 1]), type datetime) <= Type.Is(Value.Type(y[Column 1]), type datetime))),
Final = Table.RenameColumns(Table.ExpandTableColumn(Group, "All", {"Product", "Quantity"}),{{"Column 1", "Date"}})
in
FinalSolving the challenge of Table Transformation! Part 21 with Excel
Excel solution 1 for Table Transformation! Part 21, proposed by Oscar Mendez Roca Farell:
=LET(
d,
C3:C24,
FILTER(
HSTACK(
SCAN(
,
d,
MAX
),
d,
MAP(
d,
LAMBDA(
a,
XLOOKUP(
0,
N(
a:C24>""
),
a:C24
)
)
)
),
d>""
)
)Excel solution 2 for Table Transformation! Part 21, proposed by Kris Jaganah:
=LET(a,
C3:C24,
b,
SEQUENCE(
ROWS(
a
)
),
c,
LAMBDA(v,
BYROW(IFNA(XLOOKUP(b+(v*TOROW(
b
)),
b,
a),
a),
LAMBDA(x,
IFERROR(TAKE(TOCOL(x/((v*x)<(v*11)),
3),
1),
)))),
VSTACK(
{"Date",
"Product",
"Quantity"},
FILTER(
HSTACK(
c(
-1
),
a,
c(
1
)
),
ISTEXT(
a
)
)
))Excel solution 3 for Table Transformation! Part 21, proposed by Ivan William:
=LET(a,
C3:C24,
b,
ROW(
a
),
FILTER(HSTACK(SCAN(
,
a,
MAX
),
a,
XLOOKUP(b,
b/(a<99),
a,
,
1)),
a>""))Excel solution 4 for Table Transformation! Part 21, proposed by Sunny Baggu:
=LET(
t,
C3:C24, _s,
SEQUENCE(
ROWS(
t
)
), _e1,
LAMBDA(
rng,
SCAN(
"",
rng,
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
)
), _a,
_e1(IF(
IFERROR(
YEAR(
t
) <> 1900,
0
) + 0,
t,
""
)), _b,
SORTBY(_e1(IFERROR(
SORTBY(
--t,
_s,
-1
),
""
)),
_s,
-1), FILTER(
HSTACK(
_a,
t,
_b
),
ISERR(
--t
)
)
)Excel solution 5 for Table Transformation! Part 21, proposed by Hamidi Hamid:
=LET(
d,
IF(
C3:C24>2000,
C3:C24,
""
),
s,
FILTER(
d,
d<>""
),
t,
SCAN(
,
IFERROR(
s*1,
0
),
MAX
),
x,
FILTER(
t,
ISTEXT(
s
)
),
y,
FILTER(
C3:C24,
ISTEXT(
C3:C24
)
),
z,
TAKE(
WRAPROWS(
DROP(
SORTBY(
SCAN(
,
IFERROR(
IF(
SORTBY(
C3:C24,
SEQUENCE(
ROWS(
C3:C24
)
),
-1
)>4000,
"",
SORTBY(
C3:C24,
SEQUENCE(
22
),
-1
)*1
),
""
),
LAMBDA(
a,
b,
IF(
b="",
a,
b
)
)
),
SEQUENCE(
ROWS(
C3:C24
)
),
-1
),
2
),
2
),
,
1
),
HSTACK(
x,
y,
z
)
)Excel solution 6 for Table Transformation! Part 21, proposed by Md. Zohurul Islam:
=LET(z,
C3:C24,
hdr,
HSTACK(
"Date",
"Product",
"Quantity"
),
a,
IFERROR(ISNUMBER(
z
)*(YEAR(
z
)>1900),
0),
b,
SCAN(
0,
IF(
a=1,
z,
0
),
MAX
),c,
UNIQUE(
b
),
d,
DROP(
REDUCE(
"",
c,
LAMBDA(
x,
y,
LET(
p,
DROP(
FILTER(
z,
b=y
),
1
),
q,
SEQUENCE(
COUNTA(
p
)
),
r,
HSTACK(
q,
p
),
s,
ISNUMBER(
p
),
t,
FILTER(
r,
NOT(
s
)
),
u,
FILTER(
r,
s
),
v,
XLOOKUP(
DROP(
t,
,
-1
),
DROP(
u,
,
-1
),
DROP(
u,
,
1
),
,
1
),
w,
VSTACK(
x,
IFNA(
HSTACK(
y,
t,
v
),
y
)
),
w
)
)
),
1
),
e,
VSTACK(
hdr,
CHOOSECOLS(
d,
1,
3,
4
)
),
e)Excel solution 7 for Table Transformation! Part 21, proposed by Pieter de B.:
=LET(
a,
C3:C24,
b,
SCAN(
,
a,
MAX
),
c,
SCAN(
0,
a,
LAMBDA(
d,
e,
IF(
ISTEXT(
e
),
XLOOKUP(
0,
--ISTEXT(
e:C24
),
e:C24
),
0
)
)
),
FILTER(
HSTACK(
b,
a,
c
),
c
)
)Solving the challenge of Table Transformation! Part 21 with Python
Python solution 1 for Table Transformation! Part 21, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
import re
path = "CH-177 Table Transformation.xlsx"
input = pd.read_excel(path, usecols="C", skiprows=1, nrows=22, names=["Column 1"])
test = pd.read_excel(path, usecols="E:G", skiprows=1, nrows=10)
input['col'] = input['Column 1'].apply(lambda x: 3 if re.match(r'^d+$', str(x)) else (2 if re.match(r'^[A-Za-z]', str(x)) else 1))
input['Date'] = np.where(input['col'] == 1, input['Column 1'], np.nan)
input['Date'] = input['Date'].ffill()
input['Quantity'] = np.where(input['col'] == 3, input['Column 1'], np.nan)
input['Quantity'] = input['Quantity'].bfill()
result = input[input['col'] == 2][['Date', 'Column 1', 'Quantity']].reset_index(drop=True)
result.columns = ['Date', 'Product', 'Quantity']
print(result.equals(test))
hashtag
#TruePython solution 2 for Table Transformation! Part 21, proposed by Luan Rodrigues:
import pandas as pd
import numpy as np
file = "CH-177 Table Transformation.xlsx"
df = pd.read_excel(file,usecols="C",skiprows=1)
df['Date'] = pd.to_datetime(df['Column 1'], format='%d%m%Y', errors='coerce').ffill()
df['Quantity'] = df['Column 1'].apply(lambda x: x if str(x).isdigit() else np.nan).bfill()
df = df[df['Column 1'].apply(lambda x: str(x).isalpha())]
df.columns = ['Product','Date', 'Quantity']
df = df[['Date','Product','Quantity']]
print(df)Solving the challenge of Table Transformation! Part 21 with Python in Excel
Python in Excel solution 1 for Table Transformation! Part 21, proposed by Alejandro Campos:
df = xl("C2:C24", headers=True)
df['Date'] = pd.to_datetime(df['Column 1'], format='%d/%m/%Y', errors='coerce').ffill()
df['Product'], df['Quantity'] = df['Column 1'].where(df['Column 1'].str.isalpha()), pd.to_numeric(df['Column 1'], errors='coerce')
df = df.dropna(subset=['Product', 'Quantity'], how='all').assign(Product=df['Product'].ffill(), Quantity=df['Quantity'].bfill())
result_df = df[['Date', 'Product', 'Quantity']].drop_duplicates().reset_index(drop=True)Python in Excel solution 2 for Table Transformation! Part 21, proposed by Seokho MOON:
import datetime as dt
df = xl("C2:C24", headers=True)
date = []
product = []
quantity = []
for row in df["Column 1"]:
if isinstance(row, dt.datetime):
date.extend([row])
elif isinstance(row, str):
date.extend([date[-1]] * (len(product) - len(date) + 1))
product.extend([row])
else:
quantity.extend([row] * (len(product) - len(quantity)))
res = pd.DataFrame({"Date": date, "Product": product, "Quantity": quantity})
resSolving the challenge of Table Transformation! Part 21 with R
R solution 1 for Table Transformation! Part 21, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-177 Table Transformation.xlsx"
input = read_excel(path, range = "C2:C24")
test = read_excel(path, range = "E2:G12")
result = input %>%
mutate(col = case_when(
str_detect(`Column 1`, "[0-9]{5}") ~ 1,
str_detect(`Column 1`, "[A-Z]{1}") ~ 2,
TRUE ~ 3)) %>%
mutate(Date = ifelse(col == 1, `Column 1`, NA)) %>%
fill(Date, .direction = "down") %>%
mutate(Quantity = ifelse(col == 3, `Column 1`, NA)) %>%
fill(Quantity, .direction = "up") %>%
filter(col == 2) %>%
select(Date, Product=`Column 1`, Quantity) %>%
mutate(Date = janitor::excel_numeric_to_date(as.numeric(Date)) %>% as.POSIXct(),
Quantity = as.numeric(Quantity))
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUESolving the challenge of Table Transformation! Part 21 with Google Sheets
Google Sheets solution 1 for Table Transformation! Part 21, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1834510698#gid=1834510698