ncalm

// This takes several seconds to evaluate
huge_array = MAKEARRAY(10000,5000,PRODUCT);

// By putting the array in a thunk, we can choose not to evaluate it
thunked_huge_array = LET(my_thunk, LAMBDA(MAKEARRAY(10000,5000,PRODUCT)),my_thunk);

// We can evaluate it by putting () at the end of the LET function's return value
evaluated_on_LET_return = LET(my_thunk, LAMBDA(MAKEARRAY(10000,5000,PRODUCT)),my_thunk());

// Or just putting () outside the LET function

ncalm

Imputer = LAMBDA(training_data, k, [distance_function], 
    LAMBDA(observation, 
        LET(
            // Identify where the missing value is on the observation
            _missing, IFERROR(observation="",TRUE), 
            IF(
                /*If there's more than one blank/error in the observation or 
                if the training data and observation have difference column counts, 
                then return an error
                */

ncalm

let
    Source = Web.BrowserContents("https://learn.microsoft.com/en-us/azure/ai-services/Translator/language-support"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(1)"}, {"Column2", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(2)"}, {"Column3", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(3)"}, {"Column4", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(4)"}, {"Column5", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(5)"}, {"Column6", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(6)"}, {"Column7", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(7)"}}, [RowSelector="DIV.table-wrapper.has-inner-focus >

ncalm

-- https://medium.com/@mail2asimmanna/another-beautiful-sql-question-from-my-business-analyst-interview-1d9fa00c0381

DROP TABLE IF EXISTS #bookings;

SELECT 
	CAST(u AS smallint) AS userid,
	CAST(d AS date) AS booking_date
INTO #bookings
FROM 
(VALUES (1,'2024-01-01')

ncalm

GETSALARYFROMTAX = LAMBDA(low, high, rate, 
    LAMBDA(tax,
        LET(
            diff, high - low,
            bracketmax, diff * rate,
            runsum, SCAN(0, bracketmax, SUM),
            XLOOKUP(
                tax, 
                runsum, 
                low + diff * (tax - runsum + bracketmax) / bracketmax, 

ncalm

IFOMITTED = LAMBDA(arg,then,IF(ISOMITTED(arg),then,arg));
SUMPRODUCT2 = LAMBDA(array, [axis], 
    SUM(IF(IFOMITTED(axis,0)=0, BYROW, BYCOL)(array, PRODUCT))
);

ncalm

LIST.ALLPAIRS = LAMBDA(list1, list2,
    LET(
        list1Col, TOCOL(list1),
        list2Col, TOCOL(list2),
        list1length, ROWS(list1Col),
        list2length, ROWS(list2Col),
        resultRows, SEQUENCE(list1length * list2length, 1),
        rowIndex1, CEILING(resultRows / list2length, 1),
        rowIndex2, MOD(resultRows - 1, list2length) + 1,
        HSTACK(INDEX(list1Col, rowIndex1), INDEX(list2Col, rowIndex2))

ncalm

// Spread an array of arguments across the parameters of a function of up to five parameters
SPREAD = LAMBDA(function, LAMBDA(arg_array,

    LET(
        arg_vector, TOCOL(arg_array),
        CHOOSE(
            ROWS(arg_vector),
            function(INDEX(arg_vector,1)),
            function(INDEX(arg_vector,1), INDEX(arg_vector, 2)),
            function(INDEX(arg_vector,1), INDEX(arg_vector, 2), INDEX(arg_vector, 3)),

ncalm

IFOMITTED = LAMBDA(arg, then, IF(ISOMITTED(arg), then, arg));

// Functions for common mathematical operators
MULTIPLY = LAMBDA(x, y, x*y);
ADD = LAMBDA(x, y, x+y);
SUBTRACT = LAMBDA(x, y, x-y);
DIVIDE = LAMBDA(x, y, x/y);

// Apply a series of functions to an array
PIPE =LAMBDA(array, functions, operator, [init], 

ncalm

IFOMITTED = LAMBDA(arg, then, IF(ISOMITTED(arg), then, arg));

BINARYSEARCH = LAMBDA(search_for, array, [stop], [iter], [left_index], [right_index], 

    LET(
        _iter, IFOMITTED(iter, 1),
        _stop, IFOMITTED(stop, ROWS(array)+1),
        _left_index, IFOMITTED(left_index, 1),
        _right_index, IFOMITTED(right_index, ROWS(array)),
        _seq, SEQUENCE(ROWS(array)),