Excel Lambda functions for Power Query List functions

EX_List

/* USE NAMESPACE "List" */
Accumulate = LAMBDA(_list, _seed, _accumulator, REDUCE(_seed, _list, _accumulator));

AllTrue = LAMBDA(_list, AND(_list));

AnyTrue = LAMBDA(_list, OR(_list));

Average = LAMBDA(_list, AVERAGE(_list));

Contains = LAMBDA(_list, _value, [_equationCriteria],
    LET(
        _islist, COLUMNS(_list) = 1,
        _isempty, ROWS(_list) = 1,
        IF(ISOMITTED(_equationCriteria), OR(_list = _value), OR(_equationCriteria(_list, _value)))
    )
);

ContainsAll = LAMBDA(_list, _values, [_equationCriteria],
    AND(List.Transform(_values, LAMBDA(_value, List.Contains(_list, _value, _equationCriteria))))
);

ContainsAny = LAMBDA(_list, _values, [_equationCriteria],
    OR(List.Transform(_values, LAMBDA(_value, List.Contains(_list, _value, _equationCriteria))))
);

Count = LAMBDA(_list, COUNTA(ISERROR(_list)));

Covariance = LAMBDA(_list1, _list2, COVARIANCE.P(_list1, _list2));

Dates = LAMBDA(_start, _count, _step, SEQUENCE(_count, 1, _start, _step));

DateTimes = LAMBDA(_start, _count, _step, SEQUENCE(_count, 1, _start, _step));

Difference = LAMBDA(_list1, _list2, [_equationCriteria],
    LET(
        existing, MAP(
            _list1,
            LAMBDA(_item,
                IF(
                    ISOMITTED(_equationCriteria),
                    List.Contains(_list2, _item),
                    List.Contains(_list2, _item, _equationCriteria)
                )
            )
        ),
        FILTER(_list1, NOT(existing))
    )
);

Distinct = LAMBDA(_list, _equationCriteria, UNIQUE(_list));

Durations = LAMBDA(_start, _count, _step, SEQUENCE(_count, 1, _start, _step));

FindText = LAMBDA(_list, _text, FILTER(_list, IFERROR(FIND(_text, _list) > 0, FALSE)));

First = LAMBDA(_list, [_defaultValue], IFERROR(INDEX(_list, 1), _defaultValue));

FirstN = LAMBDA(_list, _countOrCondition,
    IF(
        ISNUMBER(_countOrCondition),
        MAKEARRAY(MIN(_countOrCondition, List.Count(_list)), 1, LAMBDA(ir, ic, INDEX(_list, ir))),
        LET(n, MATCH(FALSE(), _countOrCondition(_list), 0) - 1, List.FirstN(_list, n))
    )
);

InsertRange = LAMBDA(_list, _index, _values,
    LET(
        ct_list, COUNTA(ISERROR(_list)),
        ct_values, COUNTA(ISERROR(_values)),
        MAKEARRAY(
            ct_list + ct_values,
            1,
            LAMBDA(ir, ic,
                IFS(
                    ir < _index,
                    INDEX(_list, ir),
                    ir < (_index + ct_values),
                    INDEX(_values, ir - _index + 1),
                    TRUE,
                    INDEX(_list, ir - ct_values)
                )
            )
        )
    )
);

IsDistinct = LAMBDA(_list, [_equationCriteria],
    List.Count(_list) = List.Count(List.Distinct(_list, _equationCriteria))
);

IsEmpty = LAMBDA(_list, AND(ISBLANK(_list)));

Item = LAMBDA(_list, _item, INDEX(_list, _item));

Last = LAMBDA(_list, [_defaultValue], IFERROR(INDEX(_list, COUNTA(ISERROR(_list))), _defaultValue));

LastN = LAMBDA(_list, _countOrCondition,
    IF(
        ISNUMBER(_countOrCondition),
        MAKEARRAY(
            MIN(_countOrCondition, List.Count(_list)),
            1,
            LAMBDA(ir, ic, INDEX(_list, COUNTA(ISERROR(_list)) - _countOrCondition + ir))
        ),
        LET(n, MATCH(FALSE(), _countOrCondition(List.Reverse(_list)), 0) - 1, List.LastN(_list, n))
    )
);

MatchesAll = LAMBDA(_list, _condition, AND(_condition(_list)));

MatchesAny = LAMBDA(_list, _condition, OR(_condition(_list)));

Max = LAMBDA(_list, MAX(_list));

MaxN = LAMBDA(_list, _countOrCondition,
    IF(
        ISNUMBER(_countOrCondition),
        MAKEARRAY(MIN(_countOrCondition, List.Count(_list)), 1, LAMBDA(ir, ic, LARGE(_list, ir))),
        LET(
            n, MATCH(FALSE(), _countOrCondition(SORT(_list, 1, -1)), 0) - 1,
            IF(ISNA(n), SORT(_list, 1, -1), List.MaxN(_list, n))
        )
    )
);

Median = LAMBDA(_list, MEDIAN(_list));

Min = LAMBDA(_list, MIN(_list));

MinN = LAMBDA(_list, _countOrCondition,
    IF(
        ISNUMBER(_countOrCondition),
        MAKEARRAY(MIN(_countOrCondition, List.Count(_list)), 1, LAMBDA(ir, ic, SMALL(_list, ir))),
        LET(
            n, MATCH(FALSE(), _countOrCondition(SORT(_list, 1, 1)), 0) - 1,
            IF(ISNA(n), SORT(_list, 1, 1), List.MinN(_list, n))
        )
    )
);

Mode = LAMBDA(_list, MODE.SNGL(_list));

Modes = LAMBDA(_list, MODE.MULT(_list));

NonNullCount = LAMBDA(_list, List.Count(List.RemoveNulls(_list)));

Numbers = LAMBDA(_start, _count, [_increment],
    SEQUENCE(_count, 1, _start, IF(ISOMITTED(_increment), 1, _increment))
);

Percentile = LAMBDA(_list, _percentiles, [_options], PERCENTILE.INC(_list, _percentiles));

PositionOf = LAMBDA(
    
    //  Parameter Declarations
    _list,
    _value,
    [_occurance],
    [_equationCriteria],
    LET(
        
        //  Help
        Help, TRIM(
            TEXTSPLIT(
                "DESCRIPTION:->Returns the position of a value in a list.
                VERSION:->1.0
                PARAMETERS:->_list as list, _value as value",
                "->",
                "
                "
            )
        ),
        
        //  Error Messages
        ErrorMessages, {
            "_list should be a single vertical column";
            "_list contains error";
            "_value contains error";
            "_occurance contains error"
        },
        
        //  Check Inputs
        _occurance, IF(ISOMITTED(_occurance), 1, _occurance),
        _err1, IF(COLUMNS(_list) > 1, #VALUE!, _list),
        _err2, IF(ISERROR(_list), #VALUE!, _value),
        _err3, IF(ISERROR(_value), #VALUE!, _value),
        _err4, IF(ISERROR(_value), #VALUE!, _value),
        ErrorArray, VSTACK(ISERROR(_err1), ISERROR(_err2), ISERROR(_err3), ISERROR(_err4)),
        Messages, FILTER(ErrorMessages, ErrorArray, ""),
        
        //  Procedure
        matching_check, --IFERROR(_list = _value, FALSE()),
        matching_pos, matching_check * SEQUENCE(List.Count(_list), 1, 1, 1),
        match_count, SUM(matching_check),
        Result, MATCH(1, --(matching_pos = LARGE(matching_pos, match_count - _occurance + 1)), 0),
        
        //  Handle Error
        Error, IF(OR(ErrorArray), 3, IF(OR(ISERROR(Result)), 2, 1)),
        
        //  Return Result
        CHOOSE(Error, Result, Help, Messages)
    )
);

PositionOfAny = LAMBDA(_list, _values, [_occurance], [_equationCriteria],
    LET(
        _pos, MAP(
            _values,
            LAMBDA(_item, List.PositionOf(_list, _item, _occurance, _equationCriteria))
        ),
        REDUCE(#N/A, _pos, LAMBDA(acc, cur, IF(ISERROR(acc), cur, MIN(acc, IFERROR(cur, acc)))))
    )
);

Positions = LAMBDA(_list, SEQUENCE(COUNTA(ISERROR(_list)), 1, 1, 1));

Product = LAMBDA(_list, PRODUCT(_list));

Random = LAMBDA(_count, MAKEARRAY(_count, 1, LAMBDA(ir, ic, RAND())));

Range = LAMBDA(_list, _offset, [_count],
    LET(
        _skipped, List.Skip(_list, _offset),
        IF(ISOMITTED(_count), _skipped, List.FirstN(_skipped, _count))
    )
);

RemoveFirstN = LAMBDA(_list, _countOrCondition,
    IF(
        ISOMITTED(_countOrCondition),
        List.RemoveFirstN(_list, 1),
        IF(
            ISNUMBER(_countOrCondition),
            List.LastN(_list, List.Count(_list) - _countOrCondition),
            LET(n, MATCH(FALSE(), _countOrCondition(_list), 0) - 1, List.RemoveFirstN(_list, n))
        )
    )
);

RemoveItems = LAMBDA(_list1, _list2,
    FILTER(_list1, MAP(_list1, LAMBDA(_item, NOT(List.Contains(_list2, _item)))))
);

RemoveLastN = LAMBDA(_list, [_countOrCondition],
    IF(
        ISOMITTED(_countOrCondition),
        List.RemoveLastN(_list, 1),
        IF(
            ISNUMBER(_countOrCondition),
            List.FirstN(_list, List.Count(_list) - _countOrCondition),
            LET(
                n, MATCH(FALSE(), List.Reverse(_countOrCondition(_list)), 0) - 1,
                List.RemoveLastN(_list, n)
            )
        )
    )
);

RemoveMatchingItems = LAMBDA(_list1, _list2, [_equationCriteria],
    FILTER(_list1, MAP(_list1, LAMBDA(_item, NOT(List.Contains(_list2, _item, _equationCriteria)))))
);

RemoveNulls = LAMBDA(_list, FILTER(_list, NOT(ISBLANK(_list))));

RemoveRange = LAMBDA(_list, _index, [_count],
    FILTER(
        _list,
        MAP(
            SEQUENCE(List.Count(_list), 1, 1, 1),
            LAMBDA(_item, NOT(AND(_item >= _index, _item < (_index + _count))))
        )
    )
);

Repeat = LAMBDA(_list, count,
    LET(
        ct, COUNTA(ISERROR(_list)),
        MAKEARRAY(ct * count, 1, LAMBDA(ir, ic, INDEX(_list, MOD(ir - 1, ct) + 1)))
    )
);

ReplaceRange = LAMBDA(_list, _index, _count, _replaceWith,
    LET(
        ctl, List.Count(_list),
        ctr, List.Count(_replaceWith),
        ctn, ctl + ctr - MIN(ctl - _index + 1, _count),
        MAP(
            SEQUENCE(ctn, 1, 1, 1),
            LAMBDA(_item,
                IFS(
                    _item < _index,
                    List.Item(_list, _item),
                    _item < (_index + ctr),
                    List.Item(_replaceWith, _item - _index + 1),
                    TRUE,
                    List.Item(_list, _item - ctr + _count)
                )
            )
        )
    )
);

ReplaceValue = LAMBDA(_list, _oldValue, _newValue, _replacer,
    MAP(_list, LAMBDA(_item, _replacer(_item, _oldValue, _newValue)))
);

Reverse = LAMBDA(_list,
    LET(ct, COUNTA(ISERROR(_list)), MAKEARRAY(ct, 1, LAMBDA(ir, ic, INDEX(_list, ct - ir + 1))))
);

Select = LAMBDA(_list, _selection,
    LET(
        selected, MAKEARRAY(ROWS(_list), 1, LAMBDA(ir, ic, _selection(INDEX(_list, ir)))),
        FILTER(_list, selected, NA())
    )
);

Single = LAMBDA(_list, IF(List.Count(_list) = 1, List.Item(_list, 1), #VALUE!));

SingleOrDefault = LAMBDA(_list, [_default],
    IF(List.Count(_list) > 1, #VALUE!, IFERROR(List.Item(_list, 1), _default))
);

Skip = LAMBDA(_list, _countOrCondition,
    IF(
        ISNUMBER(_countOrCondition),
        MAKEARRAY(
            ROWS(_list) - _countOrCondition,
            1,
            LAMBDA(ir, ic, INDEX(_list, ir + _countOrCondition))
        ),
        LET(n, MATCH(FALSE(), _countOrCondition(_list), 0) - 1, List.Skip(_list, n))
    )
);

Sort = LAMBDA(_list, SORT(_list));

StandardDeviation = LAMBDA(_list, STDEV.S(_list));

Sum = LAMBDA(_list, SUM(_list));

Transform = LAMBDA(_list, _transform, MAP(_list, _transform));