ncalm

Sub GetThemeColorsForPythonPalette()
    Dim i As Integer
    Dim colorHex As String
    Dim pythonCode As String
    Dim colorsArray() As String
    Dim themeColor As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        If ws.Name = "Theme Colors" Then

ncalm

/* Takes a name in First Last format and produces a regex string
of variants of that name that might be found before the @ in an email
address.
Optional parameters can be set to FALSE to exclude specific variants
*/

EMAILVARIANTS = LAMBDA(
    name, [firstlast], [finitlast], [firstlinit],
          [firstdotlast], [finitdotlast], [firstunderlast],
    LET(

ncalm

/*
axis is (default) 0 for rows 1 for columns
array is a 2D array
at is a position at which to partition the array on the axis

So this returns two thunks, stacked vertically, the first
of which contains the first 5 rows of the array. The second contains the second
5 rows of the array. 

PARTITION(0)(SEQUENCE(10,10), 5)

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))