This challenge was contributed by Taeyong Shin Unmerge merged cells, separate characters separated by delimiters, and create a normalized table.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 409
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Normalize Table from Merged Cells with Power Query
Power Query solution 1 for Normalize Table from Merged Cells, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.ExpandListColumn(
Table.TransformColumns(
Table.FillDown(
Table.ReplaceValue(Source, "", null, Replacer.ReplaceValue, {"Note"}),
{"Categories", "Note"}
),
{"Items", Splitter.SplitTextByDelimiter(", ")}
),
"Items"
)
in
AnsPower Query solution 2 for Normalize Table from Merged Cells, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
n = "Note", i = "Items",
A = Table.FillDown(Table.ReplaceValue(S, "", null, Replacer.ReplaceValue, {n}), {"Categories", n}),
R = Table.ExpandListColumn(Table.TransformColumns(A, {i, each Text.Split(_, ", ")}), i)
in
R
Blessings!
Power Query solution 3 for Normalize Table from Merged Cells, proposed by Zoran Milokanović:
let
Source = Table.FillDown(
Table.ReplaceValue(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"",
null,
Replacer.ReplaceValue,
{"Note"}
),
{"Categories", "Note"}
),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each Text.Split(_{2}, ", "),
(r, _) => List.ReplaceRange(r, 2, 1, {_})
),
Table.ColumnNames(Source)
)
in
SPower Query solution 4 for Normalize Table from Merged Cells, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Date = Table.TransformColumnTypes(Source, {{"Date", type date}}),
Split = Table.ExpandListColumn(
Table.TransformColumns(Date, {"Items", Splitter.SplitTextByDelimiter(", ")}),
"Items"
),
ColName = Table.ColumnNames(Split),
Replace = Table.ReplaceValue(Split, "", null, Replacer.ReplaceValue, ColName),
FillDown = Table.FillDown(Replace, ColName)
in
FillDownPower Query solution 5 for Normalize Table from Merged Cells, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
NoBlanks = Table.ReplaceValue(Origen,"",null,Replacer.ReplaceValue, Table.ColumnNames(Origen)),
FillD = Table.FillDown(NoBlanks,Table.ColumnNames(Origen)),
Split = Table.TransformColumns(FillD, {"Items", each Text.Split(_,",")}),
Sol = Table.ExpandListColumn(Split, "Items")
in
Sol
Show translation
Show translation of this commentPower Query solution 6 for Normalize Table from Merged Cells, proposed by Luan Rodrigues:
let
Fonte = Table.ReplaceValue(
Tabela1,
each "",
each null,
Replacer.ReplaceValue,
Table.ColumnNames(Tabela1)
),
tab = Table.TransformColumns(Fonte, {{"Items", each Text.Split(_, ", ")}}),
exp = Table.ExpandListColumn(tab, "Items"),
res = Table.FillDown(exp, Table.ColumnNames(exp))
in
resPower Query solution 7 for Normalize Table from Merged Cells, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.FillDown(S, {"Categories", "Note"}),
b = Table.TransformColumns(a, {"Items", each Text.Split(_, ", ")}),
Sol = Table.ExpandListColumn(b, "Items")
in
SolPower Query solution 8 for Normalize Table from Merged Cells, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"Categories", type text},
{"Date", type datetime},
{"Items", type text},
{"Companies", type text},
{"Note", type text}
}
),
#"Filled Down" = Table.FillDown(#"Changed Type", {"Categories"}),
#"Replaced Value" = Table.ReplaceValue(#"Filled Down", "", null, Replacer.ReplaceValue, {"Note"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(
Table.TransformColumns(
#"Replaced Value",
{
{
"Items",
Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Items"
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{{"Items", type text}}
),
#"Filled Down1" = Table.FillDown(#"Changed Type1", {"Note"})
in
#"Filled Down1"Power Query solution 9 for Normalize Table from Merged Cells, proposed by Nicolas Micot:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Type modifié" = Table.TransformColumnTypes(
#"En-têtes promus",
{
{"Categories", type text},
{"Date", type date},
{"Items", type text},
{"Companies", type text},
{"Note", type text}
}
),
#"Rempli vers le bas" = Table.FillDown(#"Type modifié", {"Categories", "Note"}),
#"Fractionner la colonne par délimiteur" = Table.ExpandListColumn(
Table.TransformColumns(
#"Rempli vers le bas",
{
{
"Items",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Items"
)
in
#"Fractionner la colonne par délimiteur"Power Query solution 10 for Normalize Table from Merged Cells, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Replaced Value" = Table.FillDown(
Table.ReplaceValue(Source, "", null, Replacer.ReplaceValue, {"Note"}),
{"Categories", "Note"}
),
#"Extracted Text" = Table.TransformColumns(
#"Replaced Value",
{{"Items", each Text.Split(_, ", "), type list}}
),
#"Expanded Items" = Table.ExpandListColumn(#"Extracted Text", "Items"),
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Items",
{
{"Categories", type text},
{"Date", type date},
{"Items", type text},
{"Companies", type text},
{"Note", type text}
}
)
in
#"Changed Type"Power Query solution 11 for Normalize Table from Merged Cells, proposed by Ernesto Vega Castillo:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(
Source,
"Items",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Items.1", "Items.2", "Items.3", "Items.4", "Items.5", "Items.6"}
),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
#"Split Column by Delimiter",
{"Categories", "Date", "Companies", "Note"},
"Attribute",
"Items"
),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns", {"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(
#"Removed Columns",
{"Categories", "Date", "Companies", "Items", "Note"}
),
#"Relleno" = Table.FillDown(#"Reordered Columns", {"Categories", "Note"})
in
#"Relleno"Power Query solution 12 for Normalize Table from Merged Cells, proposed by Amit Patel:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FillDown = Table.FillDown(Source, {"Categories"}),
Date = Table.TransformColumnTypes(FillDown, {{"Date", type date}}),
ItemsSplitted = Table.ExpandListColumn(
Table.TransformColumns(
Date,
{
{
"Items",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Items"
),
Trim = Table.TransformColumns(ItemsSplitted, {{"Items", Text.Trim, type text}})
in
TrimSolving the challenge of Normalize Table from Merged Cells with Excel
Excel solution 1 for Normalize Table from Merged Cells, proposed by Bo Rydobon 🇹🇭:
=LET(
h,
TRANSPOSE(
SCAN(
,
TRANSPOSE(
A2:E12
),
LAMBDA(
a,
v,
IF(
v>"",
v,
a
)
)
)
),
REDUCE(
A1:E1,
SEQUENCE(
ROWS(
h
)
),
LAMBDA(
a,
r,
LET(
i,
INDEX(
h,
r
),
VSTACK(
a,
CHOOSE(
{1,
2,
3,
4,
5},
i,
i,
TEXTSPLIT(
INDEX(
i,
3
),
,
", "
),
i,
i
)
)
)
)
)
)Excel solution 2 for Normalize Table from Merged Cells, proposed by John V.:
=LET(
f,
LAMBDA(
c,
SCAN(
,
c,
LAMBDA(
a,
v,
IF(
v="",
a,
v
)
)
)
),
d,
HSTACK(
f(
A2:A12
),
B2:D12,
f(
E2:E12
)
),
REDUCE(
A1:E1,
SEQUENCE(
ROWS(
d
)
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
x,
INDEX(
d,
v,
x
)
),
VSTACK(
a,
CHOOSE(
{1,
2,
3,
4,
5},
b(
1
),
b(
2
),
TEXTSPLIT(
b(
3
),
,
", "
),
b(
4
),
b(
5
)
)
)
)
)
)
)Excel solution 3 for Normalize Table from Merged Cells, proposed by John V.:
= "=LET(f,LAMBDA(c,SCAN(,c,LAMBDA(a,v,IF(v="""",a,v)))),d,HSTACK(f(A2:A12),B2:D12,f(E2:E12)),REDUCE(A1:E1,SEQUENCE(ROWS(d)),LAMBDA(a,v,LET(b,LAMBDA(x,INDEX(d,v,x)),VSTACK(a,CHOOSE({1,2,3,4,5},b(1),b(2),TEXTSPLIT(b(3),,"", ""),b(4),b(5)))))))"
and press Enter with the cursor at the end.
If you see in detail,
you can see that the only change I did was put each quote twice. Now,
you can see the Local Formula (translated into your language)Excel solution 4 for Normalize Table from Merged Cells, proposed by محمد حلمي:
=LET(
q,
A2:A12,
w,
LAMBDA(
x,
SCAN(
0,
x,
LAMBDA(
a,
d,
IF(
d="",
a,
d
)
)
)
),
r,
w(
q
),
HSTACK(
REDUCE(
A1,
UNIQUE(
r
),
LAMBDA(
a,
d,
LET(
i,
TEXTSPLIT(
TEXTJOIN(
", ",
,
FILTER(
C2:C12,
r=d
)
),
,
", "
),
VSTACK(
a,
IF(
SEQUENCE(
ROWS(
i
)
),
d
)
)
)
)
),
REDUCE(
B1:E1,
ROW(
q
)-1,
LAMBDA(
a,
d,
LET(
i,
TEXTSPLIT(
INDEX(
C2:C12,
d
),
,
", "
),
VSTACK(
a,
SORTBY(
HSTACK(
i,
IF(
SEQUENCE(
ROWS(
i
)
),
HSTACK(
INDEX(
B2:B12,
d
),
INDEX(
D2:D12,
d
),
INDEX(
w(
E2:E12
),
d
)
)
)
),
{2,
1,
3,
4}
)
)
)
)
)
)
)Excel solution 5 for Normalize Table from Merged Cells, proposed by Julian Poeltl:
=LET(
T,
A1:E12,
I,
TOCOL(
IFERROR(
TEXTSPLIT(
TEXTJOIN(
", ",
,
CHOOSECOLS(
T,
3
)
),
", ",
", ",
TRUE
),
""
)
),
ONE,
SCAN(
"",
XLOOKUP(
"*"&I&"*",
CHOOSECOLS(
T,
3
),
CHOOSECOLS(
T,
1
),
,
2
),
LAMBDA(
A,
B,
IF(
B<>"",
B,
A
)
)
),
TWO,
XLOOKUP(
"*"&I&"*",
CHOOSECOLS(
T,
3
),
CHOOSECOLS(
T,
2
),
,
2
),
FOUR,
XLOOKUP(
"*"&I&"*",
CHOOSECOLS(
T,
3
),
CHOOSECOLS(
T,
4
),
,
2
),
FIVE,
SCAN(
"",
XLOOKUP(
"*"&I&"*",
CHOOSECOLS(
T,
3
),
CHOOSECOLS(
T,
5
),
,
2
),
LAMBDA(
A,
B,
IF(
B<>"",
B,
A
)
)
),
HSTACK(
ONE,
TWO,
I,
FOUR,
FIVE
)
)Excel solution 6 for Normalize Table from Merged Cells, proposed by Julian Poeltl:
=LET(
T,
A1:E12,
TT,
CHOOSECOLS(
T,
3
),
I,
TEXTSPLIT(
TEXTJOIN(
";",
,
TT
),
,
{";",
","}
),
ONE,
SCAN(
"",
XLOOKUP(
"*"&I&"*",
TT,
CHOOSECOLS(
T,
1
),
,
2
),
LAMBDA(
A,
B,
IF(
B<>"",
B,
A
)
)
),
TWO,
XLOOKUP(
"*"&I&"*",
TT,
CHOOSECOLS(
T,
2
),
,
2
),
FOUR,
XLOOKUP(
"*"&I&"*",
TT,
CHOOSECOLS(
T,
4
),
,
2
),
FIVE,
SCAN(
"",
XLOOKUP(
"*"&I&"*",
TT,
CHOOSECOLS(
T,
5
),
,
2
),
LAMBDA(
A,
B,
IF(
B<>"",
B,
A
)
)
),
HSTACK(
ONE,
TWO,
I,
FOUR,
FIVE
)
)Excel solution 7 for Normalize Table from Merged Cells, proposed by Timothée BLIOT:
=LET(
I,
INDEX,
S,
SEQUENCE,
R,
REDUCE,
T,
A2:E12,
Sp,
LAMBDA(
x,
SCAN(
"",
x,
LAMBDA(
a,
v,
IF(
v<>"",
v,
a
)
)
)
),
Ex,
LAMBDA(
N,
DROP(
R(
"",
S(
COLUMNS(
N
)
),
LAMBDA(
a,
v,
HSTACK(
a,
Sp(
I(
N,
,
v
)
)
)
)
),
,
1
)
),
D,
Ex(
T
),
Ex(
IFNA(
DROP(
R(
"",
S(
ROWS(
D
)
),
LAMBDA(
a,
v,
LET(
M,
TEXTSPLIT(
I(
D,
v,
3
),
,
", ",
1
),
VSTACK(
a,
HSTACK(
I(
D,
v,
1
),
I(
D,
v,
2
),
M,
I(
D,
v,
4
),
I(
D,
v,
5
)
)
)
)
)
),
1
),
""
)
)
)Excel solution 8 for Normalize Table from Merged Cells, proposed by Hussein SATOUR:
=LET(
f,
LAMBDA(
x,
SCAN(
,
x,
LAMBDA(
y,
z,
IF(
z="",
y,
z
)
)
)
),
Ca,
f(
A2:A12
),
Da,
B2:B12,
It,
C2:C12,
Co,
D2:D12,
No,
f(
E2:E12
),
a,
Ca&";"&Da&";"&Co&";"&No,
CHOOSECOLS(
TEXTSPLIT(
CONCAT(
a&";"&SUBSTITUTE(
It,
",",
"|"&a&";"
)&"|"
),
";",
"|",
1
),
1,
2,
5,
3,
4
)
)Excel solution 9 for Normalize Table from Merged Cells, proposed by Duy Tùng:
=LET(
a,
SCAN(
"",
TRANSPOSE(
A2:E12
),
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
REDUCE(
A1:E1,
C2:C12,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
b,
TEXTSPLIT(
y,
,
", "
),
MAKEARRAY(
ROWS(
b
),
5,
LAMBDA(
z,
t,
IF(
t=3,
INDEX(
b,
z
),
INDEX(
a,
t,
ROW(
y
)-1
)
)
)
)
)
)
)
)
)Excel solution 10 for Normalize Table from Merged Cells, proposed by Sunny Baggu:
=LET(
_rng,
TRANSPOSE(
SCAN(
"",
TRANSPOSE(
A2:E12
),
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
)
),
REDUCE(
A1:E1,
SEQUENCE(
ROWS(
_rng
)
),
LAMBDA(
a,
v,
VSTACK(
a,
IFNA(
HSTACK(
INDEX(
_rng,
v,
1
),
INDEX(
_rng,
v,
2
),
TEXTSPLIT(
INDEX(
_rng,
v,
3
),
,
", "
),
INDEX(
_rng,
v,
4
),
INDEX(
_rng,
v,
5
)
),
INDEX(
_rng,
v,
)
)
)
)
)
)Excel solution 11 for Normalize Table from Merged Cells, proposed by LEONARD OCHEA 🇷🇴:
=LET(
S,
LAMBDA(
x,
SCAN(
"",
x,
LAMBDA(
a,
b,
IF(
b="",
a,
b
)
)
)
),
REDUCE(
A1:E1,
SEQUENCE(
ROWS(
C2:C12
)
),
LAMBDA(
x,
y,
LET(
I,
LAMBDA(
u,
INDEX(
u,
y
)
),
d,
TEXTSPLIT(
I(
C2:C12
),
,
", "
),
VSTACK(
x,
DROP(
REDUCE(
"",
d,
LAMBDA(
a,
b,
VSTACK(
a,
HSTACK(
I(
S(
A2:A12
)
),
I(
B2:B12
),
b,
I(
S(
D2:D12
)
),
I(
S(
E2:E12
)
)
)
)
)
),
1
)
)
)
)
)
)
Just one input data t:A1:E12
=LET(
t,
A1:E12,
d,
DROP(
t,
1
),
h,
TAKE(
t,
1
),
C,
LAMBDA(
i,
INDEX(
d,
,
i
)
),
S,
LAMBDA(
x,
SCAN(
"",
x,
LAMBDA(
a,
b,
IF(
b="",
a,
b
)
)
)
),
REDUCE(
h,
SEQUENCE(
ROWS(
C(
3
)
)
),
LAMBDA(
x,
y,
LET(
I,
LAMBDA(
u,
INDEX(
u,
y
)
),
d,
TEXTSPLIT(
I(
C(
3
)
),
,
", "
),
VSTACK(
x,
DROP(
REDUCE(
"",
d,
LAMBDA(
a,
b,
VSTACK(
a,
HSTACK(
I(
S(
C(
1
)
)
),
I(
C(
2
)
),
b,
I(
S(
C(
4
)
)
),
I(
S(
C(
5
)
)
)
)
)
)
),
1
)
)
)
)
)
)Excel solution 12 for Normalize Table from Merged Cells, proposed by Asheesh Pahwa:
=LET(Ct,
F34:F44,
nt,
J34:J44,
s,
SCAN(
"",
ct,
LAMBDA(
x,
y,
IF(
y>"",
y,
x
)
)
),
t,
SCAN(
"",
nt,
LAMBDA(
x,
y,
IF(
y>"",
y,
x
)
)
),
h,
HSTACK(
s,
G34:144,
t
),
d,
DROP(REDUCE("'",
SEQUENCE (ROWS(
h
)),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TEXTSPLIT(
INDEX(
h,
y,
3
),
,
", "
),
b,
INDEX(
h,
y,
1
),
c,
INDEX(
h,
y,
2
),
d,
INDEX(
h,
y,
4
),
e,
INDEX(
h,
y,
5
),
b&"|"&c&"|"&a&"|"&d&" | "&e
)
)
)),
1),
DROP(REDUCE("",
d,
LAMBDA(acc,
itr,
VSTACK (acc,
TEXTSPLIT(
itr,
"|"
)))),
1))Excel solution 13 for Normalize Table from Merged Cells, proposed by Charles Roldan:
=LET(
SplitOn,
LAMBDA(
n,
d,
LAMBDA(
x,
HSTACK(
TAKE(
x,
,
n - 1
),
TEXTSPLIT(
INDEX(
x,
n
),
,
d
),
DROP(
x,
,
n
)
)
)
),
Unmerge,
LAMBDA(
x,
TRANSPOSE(
SCAN(
,
TRANSPOSE(
x
),
LAMBDA(
a,
b,
IF(
IFERROR(
LEN(
b
),
),
b,
a
)
)
)
)
),
R,
LAMBDA(
f,
LAMBDA(
x,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
x
)
),
LAMBDA(
a,
b,
VSTACK(
a,
f(
INDEX(
x,
b
)
)
)
)
),
1
)
)
),
B,
LAMBDA(
f,
g,
LAMBDA(
x,
f(
g(
x
)
)
)
),
B(
B(
Unmerge,
R(
SplitOn(
3,
", "
)
)
),
Unmerge
)
)(A1:E12)Excel solution 14 for Normalize Table from Merged Cells, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
f,
LAMBDA(
r,
SCAN(
"",
r,
LAMBDA(
a,
i,
IF(
i="",
a,
i
)
)
)
),
m,
MAP(
f(
A2:A12
),
B2:B12,
C2:C12,
D2:D12,
f(
E2:E12
),
LAMBDA(
a,
b,
c,
d,
e,
LET(
f,
TEXTSPLIT(
c,
", "
),
ARRAYTOTEXT(
a&"|"&b&"|"&f&"|"&d&"|"&e
)
)
)
),
TEXTSPLIT(
ARRAYTOTEXT(
m
),
"|",
", "
)
)Excel solution 15 for Normalize Table from Merged Cells, proposed by Burhan Cesur:
=LET(
f,
LAMBDA(
c,
SCAN(
,
c,
LAMBDA(
a,
v,
IF(
v="",
a,
v
)
)
)
),
d,
HSTACK(
f(
A2:A12
),
B2:D12,
f(
E2:E12
)
),
REDUCE(
A1:E1,
SEQUENCE(
ROWS(
d
)
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
x,
INDEX(
d,
v,
x
)
),
IFNA(
VSTACK(
a,
HSTACK(
b(
1
),
b(
2
),
TEXTSPLIT(
b(
3
),
,
", "
),
b(
4
),
b(
5
)
)
),
INDEX(
d,
v
)
)
)
)
)
)Excel solution 16 for Normalize Table from Merged Cells, proposed by José Antônio Morato de Carvalho:
=LET(_categories,A1:A12, _date,B1:B12, _Itens,C1:C12, _companies,D1:D12, _note,E1:E12,
_qtde,MAP(_Itens,LAMBDA(a,LEN(a)-LEN(SUBSTITUTE(a,",",""))+1)),
_condiction,SEQUENCE(,MAX(_qtde)),
_result,HSTACK(
TOCOL(IF(_condiction<=_qtde,SCAN(,_categories,LAMBDA(a,b,IF(b=0,a,b))),hashtag#N/A),3),
TOCOL(IF(_condiction<=_qtde,_date,hashtag#N/A),3),
UNIQUE(SCAN("Items",TEXTSPLIT(TEXTJOIN("*",,_Itens)&"*",,{", ";"*"},1),LAMBDA(a,b,IF(ISNUMBER(--b),a,b)))),
TOCOL(IF(_condiction<=_qtde,_companies,hashtag#N/A),3),
TOCOL(IF(_condiction<=_qtde,SCAN(,_note,LAMBDA(a,b,IF(OR(b=0,b=""),a,b))),hashtag#N/A),3)),
_result)Solving the challenge of Normalize Table from Merged Cells with Python
Python solution 1 for Normalize Table from Merged Cells, proposed by Cristobal Salcedo Beltran:
https://github.com/cristobalsalcedo90/BI_Challenges
code:
import pandas as pd
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_409 - Table_Regular.xlsx"
df = pd.read_excel(file_path, usecols="A:E",nrows=11)
df['Items'] = df['Items'].str.split(',')
df_exploded = df.explode('Items')
df_exploded['Items'] = df_exploded['Items'].str.strip()
df_filled = df_exploded.fillna(method='ffill')
df_filled.reset_index(drop=True, inplace=True)
df_filled.head(200)
Python solution 2 for Normalize Table from Merged Cells, proposed by Cristobal Salcedo Beltran:
https://github.com/cristobalsalcedo90/BI_Challenges
Code:
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, explode, split, last, monotonically_increasing_id
from pyspark.sql.window import Window
import pandas as pd
spark = SparkSession.builder.appName("fill_down_example").getOrCreate()
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_409 - Table_Regular.xlsx"
df_pandas = pd.read_excel(file_path, usecols="A:E")
spark_df = spark.createDataFrame(df_pandas)
spark_df1 = spark_df.withColumn("Items", split(col("Items"), ","))
spark_df2 = spark_df1.withColumn("Items", explode("Items"))
spark_df2 = spark_df2.withColumn("row_id", monotonically_increasing_id())
windowSpec = Window.orderBy("row_id").rowsBetween(Window.unboundedPreceding, Window.currentRow)
spark_df_filled = spark_df2.withColumn("Categories", last("Categories", ignorenulls=True).over(windowSpec))
.withColumn("Note", last("Note", ignorenulls=True).over(windowSpec))
spark_df_final = spark_df_filled.drop("row_id")
spark_df_final.show(truncate=False)
Solving the challenge of Normalize Table from Merged Cells with Python in Excel
Python in Excel solution 1 for Normalize Table from Merged Cells, proposed by John V.:
Hi everyone!
One [Py] Option could be:
d = (
xl("A1:E12", headers=True)
.replace({'Note': {'': None}})
.fillna(method='ffill')
.assign(Items=lambda x: x['Items'].str.split(', '))
.explode('Items')
)
Blessings!
Python in Excel solution 2 for Normalize Table from Merged Cells, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_409 - Table_Regular.xlsx'
df = pd.read_excel(file_path, usecols='A:E').dropna(subset=['Date'])
df.ffill(inplace=True) # new in pandas 2
# Create columns by splitting column 'Items' and add columns to original df
df = pd.concat([df, df['Items'].str.split(', ', expand=True)], axis=1)
df.drop(columns=['Items'], inplace=True)
df['Ordering'] = range(len(df))
keep_columns = ['Categories', 'Date', 'Companies', 'Note','Ordering'] # Columns to keep as is
unpivot_columns = list(range(6)) # Columns to unpivot
df.sort_values(by=['Ordering', 'Variable'], inplace=True)
df = df.loc[:, ['Categories', 'Date', 'Items', 'Companies', 'Note']]
df.dropna(subset=['Items'], inplace=True, ignore_index=True) # ignore_index new in pandas 2
print(df)
Solving the challenge of Normalize Table from Merged Cells with R
R solution 1 for Normalize Table from Merged Cells, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/409 Table_Regular.xlsx", range = "A1:E12")
test = read_excel("Excel/409 Table_Regular.xlsx", range = "G1:K29")
result = input %>%
fill(c(1,5), .direction = "down") %>%
mutate(Items = str_split(Items, ", ")) %>%
unnest_longer(Items)
&