Built to create a simple pivot table using output from SUMMARIZE then picking out the unique values for the row and column grouping and doing a look-up off hashed values.
Uses same functions as SUMMARIZE, plus the hash functions below:
hashFunc
hashArray
Excel Formula:
=LAMBDA(
tbl, // Table reference
tbl_hdrs, // Table1[#Headers], needed if tbl is from FILTER
row_attr_idxs, // = # or {#,#,#,...}, 1-D
col_attr_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 - Combine attr idxs
groupby_idxs,hStack(row_attr_idxs,col_attr_idxs),
// Step 1b - Summarize
summary,
summarize(tbl,tbl_hdrs,groupby_idxs,val_idxs,agg_fn_idxs),
// Step 1c - Get summary hdrs and data
summary_hdrs,vHead(summary),
summary_data,vTail(summary),
// Step 1d - Determine rel pos of row attrs
row_rel_idxs,MATCH(row_attr_idxs,groupby_idxs,0),
// Step 1e - Determine rel pos of col attrs
col_rel_idxs,MATCH(col_attr_idxs,groupby_idxs,0),
// Step 1f - Determine rel pos of val cols
val_rel_idxs,SEQUENCE(,COLUMNS(val_idxs),COLUMNS(groupby_idxs)+1,1),
// Step 1f - Combine rel idxs
rel_idxs,hStack(row_rel_idxs,col_rel_idxs),
// Step 1g - Select attr cols from tbl
attr_cols,pickCol(tbl,groupby_idxs),
// Step 1h - Sort unique attr combinations
sort_uniq_attrs,SORT(UNIQUE(attr_cols),rel_idxs),
// Step 2 - Helper function
getTblHdr,
LAMBDA(hdr_ref,idxs,
INDEX(hdr_ref,1,idxs)),
// Step 3a - Row headers
row_hdrs,getTblHdr(summary_hdrs,row_rel_idxs),
// Step 3b - Col headers
col_hdrs,getTblHdr(summary_hdrs,col_rel_idxs),
// Step 3c - Val headers
val_hdrs,getTblHdr(summary_hdrs,val_rel_idxs),
// Step 3d - Create row labels
row_labels,
SORT(
UNIQUE(pickCol(summary_data,row_rel_idxs)),
SEQUENCE(,COLUMNS(row_rel_idxs))
),
// Step 3e - Attr for col labels
col_attr_vals,
SORT(
UNIQUE(pickCol(summary_data,col_rel_idxs)),
SEQUENCE(,COLUMNS(col_rel_idxs))
),
// Step 3f - Summary agg vals
agg_vals,pickCol(summary_data,val_rel_idxs),
// Step 3g - Create column labels
col_labels,
TRANSPOSE(
allPairs(
col_attr_vals,
TRANSPOSE(val_hdrs)
)
),
// Step 3h - Create hashes for attributes
hash_uniq_attrs,BYROW(sort_uniq_attrs,hashArray),
// Step 3i - Hash prior step w/ agg labels
summary_hashes,
pairWise(hash_uniq_attrs,val_hdrs,
hashFunc
),
// Step 3j - Reshape hashes into single column
flatten_hashes,
REDUCE(
/*null*/,
summary_hashes,
LAMBDA(flattened,hash,
vStack(flattened,hash)
)),
// Step 3k - Reshape vals into single column
flatten_vals,
REDUCE(
/*null*/,
agg_vals,
LAMBDA(flattened,val,
vStack(flattened,val)
)),
// Step 3l - Create hashes for row labels
row_hashes,BYROW(row_labels,hashArray),
// Step 3m - Create hashes for col labels
col_hashes,BYCOL(col_labels,hashArray),
// Step 3n - Combine row and col hashes
data_hashes,
pairWise(row_hashes,col_hashes,hashFunc),
// Step 3o - Look-up summary vals for final
data_vals,
INDEX(
flatten_vals,
MATCH(data_hashes,flatten_hashes,0)),
// Step 4a - Stack row attr hdrs and labels
row_hdrs_and_labels,
vStack(row_hdrs,row_labels),
// Step 4b - Stack col attr hdrs and labels
col_hdrs_and_labels,
vStack(col_hdrs,col_labels),
// Step 4c - Whitespace padding for top-left corner
padding,
pairWise(col_labels,row_hdrs_and_labels,
LAMBDA(x,y,"")),
// Step 4d - Stack padding and row details
LHS,vStack(padding,row_hdrs_and_labels),
// Step 4e - Stack col details and data vals
RHS,vStack(col_hdrs_and_labels,data_vals),
// Step 4f - Stack LHS and RHS side-by-side
final,hStack(LHS,RHS),
final
)
)Uses same functions as SUMMARIZE, plus the hash functions below:
hashFunc
Excel Formula:
=LAMBDA(x,y,x&CHAR(1)&y);hashArray
Excel Formula:
=LAMBDA(array,REDUCE(,array,hashFunc));
Upvote
0
