5g Functions for Excel: CrtIdxλ

BXL LAMBDA 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)
  );