Numerous Methods in Excel and Power Query - Indexing Blank Cells
- V E Meganathan
- 2 days ago
- 1 min read
In this post, I will demonstrate numerous methods in Excel and Power Query to accomplish a task.
I took this challenge from the website listed below.
Task:
Indexing Blank Cells.
Method 1:
=IF(B3:D14="","B"&SCAN(0,B3:D14="",SUM),B3:D14)
Method 2:
=LET(a,B3:D14,b,TOCOL(a),c,SEQUENCE(ROWS(b)),d,BYROW((b="")*(TOROW(b)="")*(c>=TOROW(c)),SUM),WRAPROWS(IF(d,"B"&d,b),3))
Method 3: Suggested by Hagia Sofia.
=LET(a,B3:D14,s,SEQUENCE,b,s(COUNTBLANK(a)),c,IF(a="",s(ROWS(a))+s(,COLUMNS(a))/10^3)),IFNA(XLOOKUP(c,SMALL(c,b),"B"&b),a))
Method 4:
Recursion in Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
fx = (L as list,n as number) =>[a = {}, b = if L{0} is null then a & {"B" & Text.From(n)} & @fx(List.Skip(L),n+1) else a & {L{0}} & @fx(List.Skip(L),n), c = if L ={} then {} else b][c],
Result = Table.FromRows(List.Split(fx(List.Combine(Table.ToRows(Source)),1),3),Table.ColumnNames(Source))
in
Result
Method 5:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result =
[
L = List.Combine(Table.ToRows(Source)),
f = List.PositionOf(L,null,2),
fin = Table.FromRows(List.Split(List.Accumulate(List.Zip({f, List.Transform(List.Positions(f), (x) => "B" & Text.From(x + 1)) }), L, (s,c) => List.ReplaceRange(s,c{0},1,{c{1}})),3), Table.ColumnNames(Source))
][fin]
in
Result
Method 6:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
L = List.Combine(Table.ToRows(Source)),
Result = Table.FromRows(List.Split(List.Generate(
() => [a = 0,b = L{0}, n = 0],
each [n] < List.Count(L),
each [b = if L{n} is null then "B" & Text.From([a] + 1) else L{n}, a = if L{n} is null then [a] + 1 else [a], n = [n] + 1],
each [b]),3), Table.ColumnNames(Source))
in
Result
Method 7: Suggested by Oleksandr Mynka
let
src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
rows = List.Combine(Table.ToRows(src)),
width = Table.ColumnCount(src),
acc = List.Accumulate(
rows,
[a = {}, n = 0],
(s,c)=> s &
[n = s[n] + (if c = null then 1 else 0)] &
[a = s[a] & {c ?? "B" & Text.From(s[n]+1)}]
)[a],
res = Table.FromRows(List.Split(acc,width),Table.ColumnNames(src))
in
res
Comments