Recursive bisection LAMBDA function for Excel, by Peter Bartholemew

excel-lambda-BMAPλ.txt

/*
Credit: Peter Bartholemew
https://www.linkedin.com/in/peterbartholomew/
https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/214174#profile

---

BMAPλ

Recursively bisects an array and applies a function to the leaf nodes. 
Useful for when the result of the Fnλ(leaf) calls are not of equal length.
As such, BMAPλ can produce ragged arrays. 

e.g. For a unique value of ID with n rows in X, create a single row with {date1, value1, date2, value2, ..., date_n, value_n}
Repeat for each unique value of ID. 

X = {
    "ID07", 44289, 3;
    "ID07", 44322, 4;
    "ID08", 45251, 7;
    "ID22", 44511, 12;
    "ID22", 45159, 2;
    "ID22", 45038, 5;
    "ID45", 45108, 16;
    "ID45", 45116, 8;
    "ID61", 45116, 32
};

Fnλ = LAMBDA(v, TOROW(FILTER(TAKE(X,,-2), TAKE(X,,1) = v)))

BMAPλ = {
    "ID07", 44289, 3, 44322, 4, "", "";
    "ID08", 45251, 7, "", "", "", "";
    "ID22", 44511, 12, 45159, 2, 45038, 5;
    "ID45", 45108, 16, 45116, 8, "", "";
    "ID61", 45116, 32, "", "", "", ""
};
*/

BMAPλ=LAMBDA(X, Fnλ,
    LET(
        n, ROWS(X),
        Y, IF(
            n > 1,
            LET(
                ℓ, n - QUOTIENT(n, 2),
                X₁, TAKE(X, ℓ),
                X₂, DROP(X, ℓ),
                Y₁, BMAPλ(X₁, Fnλ),
                Y₂, BMAPλ(X₂, Fnλ),
                IFERROR(VSTACK(Y₁, Y₂), "")
            ),
            Fnλ(X)
        ),
        Y
    )
);