Filter rows with quantity equal to 9 and display the entire result in unhidden cells. If rows are hidden within the result area, adjust the result accordingly. For instance, if rows 6 and 7 are hidden, instead of showing the result in I3:K8, it will be displayed in I3:K10, excluding hidden rows .
📌 Challenge Details and Links
Challenge Number: 7
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Presenting The Whole Result On The Unhide Cells! with Power Query
Power Query solution 1 for Presenting The Whole Result On The Unhide Cells!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
C = Table.TransformColumnTypes(
Source,
{
{"Invoice Num", type text},
{"Product", type text},
{"Quantity", Int64.Type},
{"H/Un", Int64.Type}
}
),
A = Table.AddIndexColumn(C, "Index", 1, 1, Int64.Type),
A2 = Table.AddColumn(
A,
"C",
each try
List.Sum(if [#"H/Un"] = 1 then List.FirstN(A[#"H/Un"], [Index]) else null)
otherwise
null
),
TB2 = Table.SelectColumns(A2, {"C"}),
C2 = C,
F = Table.SelectRows(C2, each ([Quantity] = 9)),
R2 = Table.RemoveColumns(F, {"H/Un"}),
TB = Table.AddIndexColumn(R2, "C", 1, 1, Int64.Type),
C3 = Table.NestedJoin(TB2, {"C"}, TB, {"C"}, "N", JoinKind.LeftOuter),
Sol = Table.ExpandTableColumn(
C3,
"N",
{"Invoice Num", "Product", "Quantity"},
{"Invoice Num", "Product", "Quantity"}
),
A3 = Table.AddColumn(Sol, "Re", each [C] <> null and [Invoice Num] = null),
A4 = Table.SelectRows(A3, each ([Re] = false)),
Fsol = Table.RemoveColumns(A4, {"Re"})
in
FsolPower Query solution 2 for Presenting The Whole Result On The Unhide Cells!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
C = Table.TransformColumnTypes(
Source,
{
{"Invoice Num", type text},
{"Product", type text},
{"Quantity", Int64.Type},
{"H/Un", Int64.Type}
}
),
A = Table.AddIndexColumn(C, "Index", 1, 1, Int64.Type),
A2 = Table.AddColumn(
A,
"C",
each try
List.Sum(if [#"H/Un"] = 1 then List.FirstN(A[#"H/Un"], [Index]) else null)
otherwise
null
),
TB2 = Table.SelectColumns(A2, {"C"}),
C2 = C,
F = Table.SelectRows(C2, each ([Quantity] = 9)),
R2 = Table.RemoveColumns(F, {"H/Un"}),
TB = Table.AddIndexColumn(R2, "C", 1, 1, Int64.Type),
C3 = Table.NestedJoin(TB2, {"C"}, TB, {"C"}, "N", JoinKind.LeftOuter),
Sol = Table.ExpandTableColumn(
C3,
"N",
{"Invoice Num", "Product", "Quantity"},
{"Invoice Num", "Product", "Quantity"}
)
in
SolSolving the challenge of Presenting The Whole Result On The Unhide Cells! with Excel
Excel solution 1 for Presenting The Whole Result On The Unhide Cells!, proposed by Bo Rydobon 🇹🇭:
=LET(
q,
D3:D21,
f,
FILTER(
B3:D21,
q=9
),
h,
SCAN(
0,
q,
LAMBDA(
a,
v,
a+SUBTOTAL(
102,
v
)
)
),
CHOOSEROWS(
f,
FILTER(
h,
h<=ROWS(
f
)
)
)
)Excel solution 2 for Presenting The Whole Result On The Unhide Cells!, proposed by محمد حلمي:
=LET(d,D3:D21=9,R,FILTER(B3:D21,d),
C,XMATCH(0,FILTER(SUBTOTAL(103,OFFSET(B3,
ROW(B3:B21)-3,)),d)),VSTACK(TAKE(R,C+1),DROP(R,C-1)))Excel solution 3 for Presenting The Whole Result On The Unhide Cells!, proposed by Kris Jaganah:
=LET(
a,
D2:D21,
b,
MAP(
a,
LAMBDA(
x,
AGGREGATE(
2,
3,
x
)
)
)*SEQUENCE(
ROWS(
a
)
),
c,
XMATCH(
b,
UNIQUE(
b
)
),
d,
FILTER(
B2:D21,
a=9
),
e,
SEQUENCE(
ROWS(
d
),
,
2
),
IFNA(
VSTACK(
"Result",
{"Invoice Num",
"Product",
"Quantity"},
DROP(
REDUCE(
"",
c,
LAMBDA(
x,
y,
VSTACK(
x,
XLOOKUP(
y,
e,
d,
""
)
)
)
),
2
)
),
""
)
)