ncalm

/*
Gets all substrings of exactly length n from string

e.g. for all susbtrings of length 5 of the string in A1
=TEXT.SUBSTRINGS(5)(A1)
*/
SUBSTRINGS = LAMBDA(length,
    LAMBDA(string,
        LET(
            substrings,MID(string,SEQUENCE(LEN(string)),length),

ncalm

ROW = LAMBDA(row,
    LAMBDA(array,INDEX(array,row,))
);

/*
Gets the first row from a list
*/
FIRST = LAMBDA(array,
    list.ROW(1)(array)
);

ncalm

namespace_author = "Owen Price";
namespace_author_url = "https://www.flexyourdata.com";
namespace_description="This namespace provides functionality for building depreciation schedules";
schedule_demo = depn.schedule(100000,10000,6,2015,depn.db,true,depn.byyear,true);
schedule_header = {"Period labels","Periods","Depreciation","Accumulated Depreciation","Depreciated Asset Value"};


/*
Author: Owen Price
https://www.flexyourdata.com

ncalm

/*****************************************************************************************
******************************************************************************************
Array of functions
******************************************************************************************

Allows for creation of an array of functions which can be passed as a parameter to another function

Original credit to: Travis Boulden

https://www.mrexcel.com/board/threads/ifanyof.1184234/

ncalm

/*
Author: OWEN PRICE
Date: 2022-08-27

Creates a single-param lambda using the supplied value of stddevs

e.g. Create a lambda function for calculating outlier thresholds
which uses 2 standard deviations as the cut-off point.

=outlier.thresholds(2)

ncalm

/*
Produces a sequence of numbers where n_i = n_(i-1) + n_(i-2)

We can optionally provide first and second values in the sequence.

Otherwise they are assumed to be 1 and 1.

*/

FIBONACCI =LAMBDA(seq_length,[first],[second],

ncalm

//returns TRUE if every list element is null, otherwise FALSE
ListAllNull = (this_list) => List.MatchesAll(this_list, each Value.Is(_, Null.Type))

ncalm

//"a" & null = "a"
//null & "b" = "b"
//And this behavior which is different to the default behavior for Text.Combine:
//null & null = null
//Default separator is an empty string
custom_combine = (columns as list,optional sep as nullable text) => 
                        if 
                            ListAllNull(columns)
                        then 
                            null 

ncalm

/*
Pass the names of tables in your Excel file to the first two parameters. 

The substitutions table must have three columns:
1) column name
2) from text
3) to text

Exact column headers aren't important. 

ncalm

/*
Author: Owen Price
Date: 2022-09-17

https://www.flexyourdata.com/

Handles multi-level nested column headers, optionally combines them into a single header row and optionally promotes the first row to the column header of the resulting table.
*/
let 
    func =