Meant to correspond loosely to DAX function SUMMARIZE; creates a tabular summary of a data table based upon chosen column attributes.
Has a few helper functions:
pickCol
aggHelper
References pairWise, thunkIt, and ifAnyOf and some of its helpers.
Excel Formula:
=LAMBDA(
tbl, // Table reference
tbl_hdrs, // Table1[#Headers], needed if tbl is from FILTER
groupby_idxs, // = # or {#,#,#,...}, 1-D
val_idxs, // = # or {#,#,#,...}, 1-D
agg_fn_idxs, // = #, {#,#,#,...}, 1-D, or custom functions
/* Custom function layout:
LAMBDA(sw,
SWITCH(sw,
"label","Example Agg of",
"function",LAMBDA(vals,fn(vals))
)
) */
LET(
// Step 1a - Sequence rel idxs
rel_idxs,SEQUENCE(,COLUMNS(groupby_idxs)),
// Step 1b - Select attr cols from tbl
attr_cols,pickCol(tbl,groupby_idxs),
// Step 1c - Sort unique attr combinations
sort_uniq_attrs,SORT(UNIQUE(attr_cols),rel_idxs),
// Step 2a - Make an array of thunked attr tests
attr_test_thunks,
MAKEARRAY(
ROWS(sort_uniq_attrs),
COLUMNS(sort_uniq_attrs),
LAMBDA(i,j,
thunkIt(
ifAnyOf(
pickCol(attr_cols,j),
isEqualTo(
INDEX(sort_uniq_attrs,i,j)
))))),
// Step 2b - Accumulate attribute tests by row
accum_attr_tests,
BYROW(
attr_test_thunks,
LAMBDA(row_,
thunkIt(
REDUCE(
TRUE,
row_,
LAMBDA(acc_bool,col_thunk,
acc_bool*col_thunk()
))))),
// Step 2c - Map an array of thunked val cols
val_col_thunks,
MAP(val_idxs,
LAMBDA(idx,
thunkIt(
pickCol(tbl,idx)
))),
// Step 2d - Map an array of agg functions
agg_funcs,
MAP(agg_fn_idxs,aggHelper("function")),
// Step 2e - Pairwise application of tests to cols
tested_vals,
pairWise(
accum_attr_tests,
val_col_thunks,
LAMBDA(test,thunk,
thunkIt(
IF(test(),thunk(),"")
))),
// Step 2f - Map agg funcs by row
aggregated_vals,
MAP(
CHOOSE(SEQUENCE(ROWS(tested_vals),1,1,0),
agg_funcs),
tested_vals,
LAMBDA(func,thunk,
func(thunk())
)),
// Step 3 - Helper function
getTblHdr,
LAMBDA(hdr_ref,idxs,
INDEX(hdr_ref,1,idxs)),
// Step 3a - Row headers
row_hdrs,getTblHdr(tbl_hdrs,groupby_idxs),
// Step 3b - Val headers
val_hdrs,getTblHdr(tbl_hdrs,val_idxs),
// Step 3c - Aggregation label
agg_labels,
MAP(agg_fn_idxs,
aggHelper("label"))&val_hdrs,
// Step 4a - Stack attr hdrs and labels
attr_hdrs_and_labels,
vStack(row_hdrs,sort_uniq_attrs),
// Step 4b - Stack agg labels and agg vals
agg_labels_and_vals,
vStack(agg_labels,aggregated_vals),
// Step 4c - Stack side-by-side
final,
hStack(
attr_hdrs_and_labels,
agg_labels_and_vals),
final
)
)Has a few helper functions:
pickCol
Excel Formula:
=LAMBDA(tbl,idxs,
INDEX(tbl,SEQUENCE(ROWS(tbl)),idxs))aggHelper
Excel Formula:
=LAMBDA(lbl_or_agg,
LAMBDA(idx_or_func,
LET(
lbls,{"AVERAGE of ","COUNT of ","COUNTA of ",
"MAX of ","MIN of ","PRODUCT of ",
"STDEV.S of ","STDEV.P of ","SUM of ",
"VAR.S of ","VAR.P of ","MEDIAN of ","MODE.SNGL of "},
SWITCH(lbl_or_agg,
"label",
IF(
ISNUMBER(idx_or_func),INDEX(lbls,idx_or_func),
idx_or_func("label")
),
"function",
IF(
ISNUMBER(idx_or_func),
LAMBDA(vals,
LET(
filtered,
FILTER(vals,
NOT(ifAnyOf(vals, isEqualTo(0)))*
NOT(ifAnyOf(vals, isEqualTo("")))),
CHOOSE(idx_or_func,
AVERAGE(filtered),
COUNT(filtered),
COUNTA(filtered),
MAX(filtered),
MIN(filtered),
PRODUCT(filtered),
STDEV.S(filtered),
STDEV.P(filtered),
SUM(filtered),
VAR.S(filtered),
VAR.P(filtered),
MEDIAN(filtered),
MODE.SNGL(filtered)
)
)
),
LAMBDA(vals,idx_or_func("function")(
FILTER(vals,
NOT(ifAnyOf(vals, isEqualTo(0)))*
NOT(ifAnyOf(vals, isEqualTo(""))))))
)
)
)
)
)References pairWise, thunkIt, and ifAnyOf and some of its helpers.
Upvote
0
