Skip to content

Instantly share code, notes, and snippets.

@halbuki

halbuki/EXLIST

Last active September 7, 2022 08:03
Show Gist options
  • Save halbuki/938025e4fc61d1ea23c2c015e830bdd3 to your computer and use it in GitHub Desktop.
Save halbuki/938025e4fc61d1ea23c2c015e830bdd3 to your computer and use it in GitHub Desktop.
Download ZIP
Excel Lambda functions for Power Query List functions
Raw
EXLIST
/* 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],
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(_list, _value, [_occurance], [_equationCriteria],
LET(
occ, IF(ISOMITTED(_occurance), 1, _occurance),
mat, --IFERROR(_list = _value, FALSE()),
tmat, SUM(mat),
pos, SEQUENCE(List.Count(_list), 1, 1, 1),
fil, mat * pos,
MATCH(1, --(fil = LARGE(fil, tmat - occ + 1)), 0)
)
);
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));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment