Skip to content

Instantly share code, notes, and snippets.

View ncalm's full-sized avatar
💭
hitting computer with hammer

Owen Price ncalm

💭
hitting computer with hammer
View GitHub Profile
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
ncalm / azure-a-translation-codes.m
Created July 20, 2024 20:10
List of language codes for use with Azure AI Language Services and the Excel TRANSLATE function
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
ncalm / frequent_users.sql
Created May 29, 2024 20:34
T-SQL solution for a question of "Find users for whom each booking is within 7 days of their most recent booking"
-- 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
ncalm / excel-lambda-GETSALARYFROMTAX.txt
Created May 27, 2024 16:19
This Excel LAMBDA function taxes a table of incremental tax brackets and a known tax amount and calculates the income that would produce that tax amount.
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
ncalm / excel-lambda-SUMPRODUCT2.txt
Created April 30, 2024 17:14
This Excel LAMBDA functions mimics SUMPRODUCT but allows a single 2D array as its only argument
IFOMITTED = LAMBDA(arg,then,IF(ISOMITTED(arg),then,arg));
SUMPRODUCT2 = LAMBDA(array, [axis],
SUM(IF(IFOMITTED(axis,0)=0, BYROW, BYCOL)(array, PRODUCT))
);
@ncalm
ncalm / excel-lambda-list.allpairs.txt
Created April 26, 2024 12:41
This Excel LAMBDA function implements the functionality of F#'s List.allPairs function for creating a cross-join between two lists (vectors)
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
ncalm / excel-lambda-SPREAD.txt
Last active April 16, 2024 23:54
This Excel LAMBDA function mimics argument unpacking for arbitrary functions of up to 5 parameters
// 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
ncalm / excel-lambda-PIPE.txt
Created April 15, 2024 23:29
Excel LAMBDA examples of function chaining
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
ncalm / excel-lambda-BINARYSEARCH.txt
Created April 4, 2024 17:09
This Excel LAMBDA function enables simulation and visualization of the binary search algorithm to find a value in a sorted array.
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)),
@ncalm
ncalm / excel-lambda-DICECOEFF.txt
Created April 2, 2024 20:16
This Excel LAMBDA function calculates the Sørensen–Dice coefficient between two text strings
/*
GETBIGRAMS - Get the bigrams of a text string
Inputs: 1. str - a text string
Returns: A vertical array of the bigrams of the text string
Example:
=GETBIGRAMS("banana")
={"ba";"an";"na";"a"}
*/