In the question table, assign an index to the blank cells, starting from 1
📌 Challenge Details and Links
Challenge Number: 182
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Add Index Column! Part 6 with Power Query
Power Query solution 1 for Add Index Column! Part 6, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Combine(Table.ToRows(Source)),
_ = Table.FromRows(
List.Split(
List.Transform(
{1 .. List.Count(L)},
(p) =>
L{p - 1} ?? "B" & Text.From(List.Count(List.Select(List.FirstN(L, p), each _ = null)))
),
3
),
Table.ColumnNames(Source)
)
in
_Power Query solution 2 for Add Index Column! Part 6, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = List.Combine(Table.ToRows(S)),
b = List.Zip({List.Positions(a),a}),
c = List.Select(b, each not List.Contains(_,null)),
d = List.Difference(b,c),
e = List.Transform({1..List.Count(d)}, each "B"& Text.From(_)),
f = List.Transform(List.Positions(e), each List.RemoveNulls(d{_}&{e{_}})),
g = List.Transform(List.Sort(f&c,{each _{0}}), each _{1}),
Sol = Table.FromRows(List.Split(g,3),Table.ColumnNames(S))
in
SolPower Query solution 3 for Add Index Column! Part 6, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Comb = List.Combine(Table.ToRows(Source)),
LG = List.Skip(
List.Generate(
() => [x = 0, y = 1],
each [x] <= List.Count(Comb),
each [
x = [x] + 1,
z = if Comb{[x]} = null then "B" & Text.From([y]) else Comb{[x]},
y = if Comb{[x]} = null then [y] + 1 else [y]
],
each [z]
)
),
Sol = Table.FromRows(List.Split(LG, 3), Table.ColumnNames(Source))
in
SolPower Query solution 4 for Add Index Column! Part 6, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Comb = List.Combine(Table.ToRows(Source)),
Lista = List.Zip({Comb, {0 .. List.Count(Comb) - 1}}),
Null = List.Select(Lista, each _{0} = null),
Bs = List.Transform({1 .. List.Count(Null)}, each "B" & Text.From(_)),
BsComb = List.Zip({Bs} & {List.Transform(Null, each _{1})}),
Sort = List.Sort(BsComb & List.Select(Lista, each _{0} <> null), each _{1}),
Sol = Table.FromRows(List.Split(List.Transform(Sort, each _{0}), 3), Table.ColumnNames(Source))
in
SolPower Query solution 5 for Add Index Column! Part 6, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Combine(Table.ToRows(A)),
C = List.Accumulate(
B,
{},
(x, y) => x & {List.Count({y}) - List.NonNullCount({y}) + List.Last(x, 0)}
),
D = List.Transform(List.Positions(C), each if B{_} = null then "B" & Text.From(C{_}) else B{_}),
E = Table.FromRows(List.Split(D, 3), Table.ColumnNames(A))
in
EPower Query solution 6 for Add Index Column! Part 6, proposed by Ankur Sharma:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Quantity", Int64.Type}}),
In1Col = List.Combine(Table.ToRows(ChangedType)),
FillTheNull = List.Transform({1..List.Count(In1Col)}, (f) =>
if In1Col{f - 1} is null
then "B" & Text.From(List.Count(List.Select(List.FirstN(In1Col, f), each _
= null)))
else In1Col{f - 1}),
ListToTable = Table.FromRows(List.Split(FillTheNull, 3),
Table.ColumnNames(Source))
in
ListToTable
Best Wishes!
Power Query solution 7 for Add Index Column! Part 6, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
List = List.Combine(Table.ToRows(Source)),
List2Table = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Index = Table.AddIndexColumn(List2Table, "Index", 0, 1, Int64.Type),
OnlyNulls = Table.SelectRows(Index, each ([Column1] = null)),
IndexforNulls = Table.AddIndexColumn(OnlyNulls, "Replace", 1, 1, Int64.Type),
Replacement = Table.TransformColumns(
IndexforNulls,
{{"Replace", each "B" & Text.From(_, "en-IN"), type text}}
),
Custom1 = Index,
Merge = Table.AddColumn(
Custom1,
"Custom",
(x) => try Table.SelectRows(Replacement, each [Index] = x[Index])[Replace]{0} otherwise null
),
Result = Table.AddColumn(Merge, "Result", each if [Column1] = null then [Custom] else [Column1])[
Result
],
Back2Table = Table.FromRows(List.Split(Result, 3), Table.ColumnNames(Source))
in
Back2TablePower Query solution 8 for Add Index Column! Part 6, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = [
Lst = List.Combine(Table.ToRows(Source)),
fin = Table.FromRows(
List.Split(
List.Transform(
List.Positions(Lst),
each
if Lst{_} is null then
"B"
& Text.From(
List.Count(List.Range(Lst, 0, _)) - List.NonNullCount(List.Range(Lst, 0, _)) + 1
)
else
Lst{_}
),
3
),
Table.ColumnNames(Source)
)
][fin]
in
ResultPower Query solution 9 for Add Index Column! Part 6, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
lst = List.Accumulate(
List.Combine(Table.ToRows(Source)),
[C = 0, L = {}],
(a, v) =>
if v <> null then
[C = a[C], L = a[L] & {v}]
else
[C = a[C] + 1, L = a[L] & {"B" & Text.From(C)}]
)[L],
Res = Table.FromRows(List.Split(lst, 3), Table.ColumnNames(Source))
in
ResPower Query solution 10 for Add Index Column! Part 6, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = List.Combine(Table.ToRows(A)),
C = {List.PositionOf(B,null,2)} & {List.Transform({1..List.Count(B) - List.NonNullCount(B)}, each "B" & Text.From(_))},
D = List.Accumulate(List.Positions(B),{}, (s,c)=> s & {B{c} ?? C{1}{List.PositionOf(C{0},c)}}),
E = Table.FromRows(List.Split(D,3), Value.Type(A))
in EPower Query solution 11 for Add Index Column! Part 6, proposed by Alison Pezzott:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
EachRecordToList = List.Combine(Table.ToRows(Source)),
ReplacedNulls = List.Accumulate(
EachRecordToList,
[NewList = {}, Counter = 1],
(state, current) =>
let
isCurrentNull = current = null,
NewValue = if isCurrentNull then "B" & Text.From(state[Counter]) else current
in
[
NewList = state[NewList] & {NewValue},
Counter = if isCurrentNull then state[Counter] + 1 else state[Counter]
]
)[NewList],
ToTable = Table.FromRows(List.Split(ReplacedNulls, 3), Table.ColumnNames(Source))
in
ToTablePower Query solution 12 for Add Index Column! Part 6, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
List = List.Combine(Table.ToRows(Source)),
Positions = List.PositionOfAny(List, {null}, Occurrence.All),
Replacements = List.Transform({1 .. List.Count(Positions)}, each "B" & Text.From(_)),
Transform = List.Transform(
{0 .. List.Count(List) - 1},
(x) =>
if List.Contains(Positions, x) then Replacements{List.PositionOf(Positions, x)} else List{x}
),
Final = Table.FromRows(List.Split(Transform, 3), Table.ColumnNames(Source))
in
FinalSolving the challenge of Add Index Column! Part 6 with Excel
Excel solution 1 for Add Index Column! Part 6, proposed by Aditya Kumar Darak 🇮🇳:
=IF(
Table1 = "",
"B" & SCAN(
0,
Table1 = "",
SUM
),
Table1
)Excel solution 2 for Add Index Column! Part 6, proposed by Oscar Mendez Roca Farell:
=IF(
B2:D14>0,
B2:D14,
"B"&SCAN(
,
B2:D14=0,
SUM
)
)Excel solution 3 for Add Index Column! Part 6, proposed by Kris Jaganah:
=LET(
a,
Table1[ #All],
IF(
a="",
"B"&SCAN(
0,
ISBLANK(
a
),
SUM
),
a
)
)Excel solution 4 for Add Index Column! Part 6, proposed by Imam Hambali:
=LET( d,
TOCOL(
Table1
), VSTACK(
Table1[ #Headers],
WRAPROWS(
IF(
d=0,
"B"&SCAN(
0,
IF(
d=0,
1,
0
),
SUM
),
d
),
3
)
))Excel solution 5 for Add Index Column! Part 6, proposed by Sunny Baggu:
=LET( _a,
TOCOL(
IF(
Table1 = "",
"",
Table1
)
), _b,
SEQUENCE(
ROWS(
_a
)
), _c,
FILTER(
_b,
_a = ""
), _d,
"B" & SEQUENCE(
ROWS(
_c
)
), WRAPROWS( IF(
_a = "",
XLOOKUP(
_b,
_c,
_d
),
_a
), 3 ))Excel solution 6 for Add Index Column! Part 6, proposed by Ankur Sharma:
=WRAPROWS( IF(
TOCOL(
Table1
) = "", "B" & SCAN(
0,
TOCOL(
Table1
) = "",
SUM
), TOCOL(
Table1
)
), 3
)Excel solution 7 for Add Index Column! Part 6, proposed by Asheesh Pahwa:
=LET(
I,
--ISBLANK(
B3:D14
), b,
"B"&SCAN(
,
BYROW(
I,
LAMBDA(
x,
SUM(
x
)
)
),
LAMBDA(
a,
v,
SUM(
a+v
)
)
),
IF(
I,
b,
B3:D14
)
)Excel solution 8 for Add Index Column! Part 6, proposed by Asheesh Pahwa:
=LET(
t,
Table1,
IF(
t<>"",
t,
"B"&SCAN(
0,
IF(
t<>"",
0,
1
),
LAMBDA(
x,
y,
SUM(
x+y
)
)
)
)
)Excel solution 9 for Add Index Column! Part 6, proposed by Eddy Wijaya:
=LET( d,
B2:D14, c,
SCAN(
0,
d,
LAMBDA(
a,
v,
IF(
v="",
a+1,
a
)
)
), IF(
d="",
"B"&c,
d
)
)Excel solution 10 for Add Index Column! Part 6, proposed by Fausto Bier:
=IF(Table1>0,Table1,"B"&SCAN(,--(Table1=""),SUM))Excel solution 11 for Add Index Column! Part 6, proposed by ferhat CK:
=LET(
a,
TOCOL(
SCAN(
0,
Table1,
LAMBDA(
a,
v,
IF(
v="",
a+1,
a
)
)
)
),
b,
TOCOL(
Table1
),
WRAPROWS(
IF(
b=0,
"B"&a,
b
),
3
)
)Excel solution 12 for Add Index Column! Part 6, proposed by Hamidi Hamid:
=LET(
x,
MAP(
B3:D14,
LAMBDA(
a,
IF(
a<>"",
0,
1
)
)
),
IF(
B3:D14="",
"B"&SCAN(
,
x,
SUM
),
B3:D14
)
)Excel solution 13 for Add Index Column! Part 6, proposed by Hussein SATOUR:
=WRAPROWS(DROP(REDUCE("B1",
TOCOL(
B3:D14
),
LAMBDA(x,
y,
VSTACK(x,
IF(y=0,
"B"&COUNTA(FILTER(x,
(LEFT(
x
)="B")*(LEN(
x
)>1))),
y)))),
1),
3)Excel solution 14 for Add Index Column! Part 6, proposed by Md. Zohurul Islam:
=LET(
z,
Table1, hdr,
Table1[ #Headers], a,
TOCOL(
z
), b,
"B"&SCAN(
0,
IF(
a=0,
1,
0
),
SUM
), c,
WRAPROWS(
IF(
a=0,
b,
a
),
COUNTA(
hdr
)
), d,
VSTACK(
hdr,
c
), d
)Excel solution 15 for Add Index Column! Part 6, proposed by Nicolas Micot:
=LET(
_data;
B3:D14; _numbers;
SCAN(
0;
_data;
LAMBDA(
l_initValue;
l_cell;
SI(
l_cell="";
l_initValue+1;
l_initValue
)
)
); SI(
_data="";
"B"&_numbers;
_data
)
)Excel solution 16 for Add Index Column! Part 6, proposed by Peter Bartholomew:
= INDEXBLANKλ(table,
"B")
where
INDEXBLANKλ
= LAMBDA(
table,
[label], LET( blank,
N(
ISBLANK(
table
)
), seq,
SCAN(
0,
blank,
SUM
), IF(
blank,
label & seq,
table
) ))Excel solution 17 for Add Index Column! Part 6, proposed by Peter Bartholomew:
= IF(
ISBLANK(
table
), "B" & SCAN(
0,
N(
ISBLANK(
table
)
),
SUM
), table
)Excel solution 18 for Add Index Column! Part 6, proposed by Pieter de B.:
=LET(
t,
Table1,
IF(
t="",
"B"&SCAN(
,
t="",
SUM
),
t
)
)Excel solution 19 for Add Index Column! Part 6, proposed by Pieter de B.:
=LET(
t,
B2:D14,
x,
t="",
IF(
x,
"B"&SCAN(
,
x,
SUM
),
t
)
)Excel solution 20 for Add Index Column! Part 6, proposed by Rick Rothstein:
=TEXTSPLIT(
REDUCE(
TEXTJOIN(
{"|",
"|",
"="},
,
IF(
B2:D14="",
-1,
B2:D14
)
),
SEQUENCE(
COUNTBLANK(
B2:D14
)
),
LAMBDA(
a,
x,
SUBSTITUTE(
a,
-1,
"B"&x,
1
)
)
),
"|",
"="
)Excel solution 21 for Add Index Column! Part 6, proposed by Trung Quan:
=WRAPROWS(
IF(
TOCOL(
Table1[
#All]
)<>"",
TOCOL(
Table1[
#All]
),
"B"&SCAN(
,
N(
TOCOL(
Table1[
#All]
)=0
),
SUM
)
),
3
)Solving the challenge of Add Index Column! Part 6 with Python
Python solution 1 for Add Index Column! Part 6, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-182 Indexing Blank cells.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=12, dtype=str)
test = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=12, dtype=str).rename(columns=lambda x: x.split('.')[0])
flat_values = input.values.flatten(order='C')
counter = 1
for i in range(len(flat_values)):
if pd.isna(flat_values[i]):
flat_values[i] = f'B{counter}'
counter += 1
result = pd.DataFrame(flat_values.reshape(input.shape), columns = input.columns)
print(result.equals(test)) # TruePython solution 2 for Add Index Column! Part 6, proposed by Seokho MOON:
df = xl("Table1[
hashtag
#All]", headers=True)
nan_mask = df.isna()
nan_cumsum = np.char.add(
"B", nan_mask.to_numpy().ravel().cumsum().reshape(df.shape).astype(str)
)
df_new = pd.DataFrame(np.where(nan_mask, nan_cumsum, df), columns=df.columns)Solving the challenge of Add Index Column! Part 6 with Python in Excel
Python in Excel solution 1 for Add Index Column! Part 6, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("Table1[
hashtag
#All]", True).T
counter = iter([f"B{i}" for i in range(1, df.isna().sum().sum() + 1)])
df = df.applymap(lambda x: next(counter) if pd.isna(x) else x).TPython in Excel solution 2 for Add Index Column! Part 6, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("Table1[
hashtag
#All]", True).T
counter = 1
def MyFun(t):
global counter
if pd.isna(t):
blnk = f"B{counter}"
counter += 1
return blnk
return t
df = df.applymap(MyFun).TPython in Excel solution 3 for Add Index Column! Part 6, proposed by Alejandro Campos:
xl("Table1[
hashtag
#Todo]", headers=True).fillna(" ")
index = 1
for i in range(len(data)):
for col in ["Date", "Product ID", "Quantity"]:
if data[col][i] == " ":
data[col][i] = f"B{index}"
index += 1
df = pd.DataFrame(data)Solving the challenge of Add Index Column! Part 6 with R
R solution 1 for Add Index Column! Part 6, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-182 Indexing Blank cells.xlsx"
input = read_excel(path, range = "B2:D14", col_types = "text")
test = read_excel(path, range = "F2:H14")
result = input %>%
mutate(rn = row_number()) %>%
pivot_longer(cols = -rn, names_to = "col", values_to = "value") %>%
arrange(rn, col) %>%
mutate(value = ifelse(is.na(value), paste0("B", cumsum(is.na(value))), value)) %>%
pivot_wider(names_from = col, values_from = value) %>%
select(-rn)
all.equal(result, test)
#> [1] TRUESolving the challenge of Add Index Column! Part 6 with Google Sheets
Google Sheets solution 1 for Add Index Column! Part 6, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1257921032#gid=1257921032