Skip to content

Instantly share code, notes, and snippets.

@CHatmaker
Last active April 21, 2024 18:35
Show Gist options
  • Save CHatmaker/e97ed39668e7d9364c973d6c5627f51e to your computer and use it in GitHub Desktop.
Save CHatmaker/e97ed39668e7d9364c973d6c5627f51e to your computer and use it in GitHub Desktop.
5g Functions for Excel: CrtIdxλ
/* FUNCTION NAME: CrtIdxλ
DESCRIPTION:*/ /**Creates an array of indexes that can be used with INDEX() can combine
all rows in all tables as would a database 'Natrual Join' or 'Cross Join.'*/
/* This was developed for multidimensional modeling. A key to multidimensional modeling
is the ability to combine all instances of all dimensions, and then access each
dimension's value. A dimension is a category of things like:
Customers, Products, Regions, Months, etc.
When faced with the situation where each dimension has a piece of information needed
for a particular calculation such as:
Monthy demand for Customer and Region = Customer[Annual Demand Count] *
Month[Seasonal Demand%] * Region[Demand %]
... we must combine all dimensions and access each instance's attributes.
The problem of combining instances of dimensions was solved long ago in relational
databses (RDBs) with SQL's "cross join" (aka Cartesian Products). Power Query has cross
join functionality but Excel's formulas do not. We can mimic RDBs cross join in Excel by:
1) Keeping each dimension in an Excel table.
2) Placing instances in those tables' rows.
3) Creating indexes over each table and making all possible index combinations
To create the combination of indexes, I created a formula that calculates an index
value for each instance of every dimension. It must be placed in every cell for
each dimension and instance combination. The number of cells needing the formula is
the product of all dimension instance counts. Thus, for 3 dimensions having
3, 4, and 5 items respectively, we must place that formula in 3*4*5 cells (60).
Once we have these indexes, we can retrieve each dimension's instance values using
Excel's INDEX() function like so:
=INDEX(Customer[Annual Demand], n)
As you might imagine, requiring a formula be repeated, potentially, hundreds or
or even thousands of times is ripe for error. Too few copies and we fail. Too many, we
fail. Inconsitent copies, we fail.
To elimnate all potentional for errors, Peter Bartholomew applied that formula to a
LAMBDA function that, when placed in a single cell, generates all indexes for all
dimensions into its #SPILL range. One formula, one cell, limitless combinations, no errors!
For an Example see: https://www.dropbox.com/s/2lbp7jtfv722rhn/BXL%20MD-TBM%20LAMBDA.xlsx?dl=1
GUILTY PARTIES: Peter Bartholomew, Craig Hatmaker 2021, 2022
*/
CrtIdxλ = LAMBDA(
// Parameter Declarations
[DimCountArray],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →CrtIdxλ(DimCountArray)¶" &
"DESCRIPTION: →Creates an array of indexes that can be used with INDEX() can combine¶" &
"→all rows in all tables as would a database 'Natrual Join' or 'Cross Join.'¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/excel-library/n-fold-cartesian-formula ¶" &
"PARAMETERS: →¶" &
"DimCountArray →(Required) This is a horizontal array containing the number of dimensions (rows) ¶" &
"→for each dimension table¶" &
"EXAMPLE: →¶" &
"→Assume two tables. The first table has two rows. The second has three.¶" &
"Formula →=CrtIdxλ( { 2, 3})¶" &
"Result →¶" &
"1→1¶" &
"2→1¶" &
"1→2¶" &
"2→2¶" &
"1→3¶" &
"2→3",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( DimCountArray),
Counter, SEQUENCE(PRODUCT(DimCountArray)),
Repetitions, SCAN(1, DimCountArray,
LAMBDA(Accumulator, Elements,
Accumulator * Elements)) / DimCountArray,
Repetition, QUOTIENT(Counter - 1, Repetitions),
Result, 1 + MOD(Repetition, DimCountArray),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/*
FUNCTION NAME: UnPivotλ
DESCRIPTION: UnPivots a 2 dimensional array using CrtIdxλ
ARGS:
Array A two dimensional array with repeating elements going
across the top, like dates, and non-repeating items going
down the first column. In the matrix are values for the
intersection of the repeating elements and items.
INTERNAL VARIABLES:
idxArray A cartesian product for the number of repeating elements
and items.
idxTop The first column of idxArray
idxLeft The second column of idxArray
Headers The first row of Array
Column1 Array's items in its first column repeated as needed
Column2 Array's repeating elements its first row repeated as needed
Column3 Array's values at the intersection of items and elements
EXAMPLE =UnPivotλ({Item,1,2,3;A,100,200,300;B,400,500,600})
GUILTY PARTIES: Craig Hatmaker 2021, 2022
*/
UnPivotλ = LAMBDA(Array,
LET(idxArray,CrtIdxλ(HSTACK(COLUMNS(Array)-1,ROWS(Array)-1)),
idxTop,CHOOSECOLS(idxArray,1),
idxLeft,CHOOSECOLS(idxArray,2),
Headers,CHOOSEROWS(Array,1),
Column1,INDEX(Array,idxLeft+1,1),
Column2,INDEX(Headers,idxTop+1),
Column3,INDEX(Array,idxLeft+1,idxTop+1),
HSTACK(Column1, Column2, Column3)
)
);
/*
FUNCTION NAME: RemoveZeroValuesλ
DESCRIPTION: Removes rows in a 2 dimensional where the 3rd column = 0
This was created to filter 0 values from UnPivotλ's results
ARGS:
Array A two dimensional array with values to filter out in the 3rd column
EXAMPLE =RemoveZeroValuesλ(UnPivotλ({Item,1,2,3;A,100,200,300;B,400,500,600}))
GUILTY PARTIES: Craig Hatmaker 2022
*/
RemoveZeroValuesλ = LAMBDA(Array,
FILTER(Array,CHOOSECOLS(Array,3)<>0)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment