Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
hitting computer with hammer

Owen Price ncalm

💭
hitting computer with hammer
View GitHub Profile
@ncalm
ncalm / powerquery-m-fixnestedheaders.txt
Last active Sep 27, 2022
This Power Query custom function will right-fill nested headers then combine n levels of headers into a single promoted column header
View powerquery-m-fixnestedheaders.txt
/*
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 =
@ncalm
ncalm / powerquery-m-multi-substitutions.txt
Created Sep 8, 2022
This Power Query function will apply a list of text substitutions to multiple columns in a table of dirty data
View powerquery-m-multi-substitutions.txt
/*
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
ncalm / powerquery-m-combiner-patterns.txt
Created Aug 31, 2022
Power Query patterns for changing default behavior when combining text values
View powerquery-m-combiner-patterns.txt
//"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
ncalm / powerquery-m-list-patterns.txt
Created Aug 31, 2022
Custom list functions and patterns for Power Query
View powerquery-m-list-patterns.txt
//returns TRUE if every list element is null, otherwise FALSE
ListAllNull = (this_list) => List.MatchesAll(this_list, each Value.Is(_, Null.Type))
@ncalm
ncalm / excel-lambda-FIBONACCI
Created Aug 28, 2022
This Excel lambda function calculates a Fibonacci-like sequence of numbers
View excel-lambda-FIBONACCI
/*
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
ncalm / excel-lambda-namespace-OUTLIERS.txt
Last active Sep 1, 2022
This Excel lambda namespace provides functions for outlier testing. Used in conjunction with the LAMB namespace.
View excel-lambda-namespace-OUTLIERS.txt
/*
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
ncalm / excel-lambda-namespace-LAMB.txt
Last active Sep 11, 2022
This Excel lambda namespace provides functionality for creating arrays of functions and sequential vector transforms
View excel-lambda-namespace-LAMB.txt
/*****************************************************************************************
******************************************************************************************
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
ncalm / excel-lambda-depn.txt
Last active Oct 3, 2022
This Excel Lambda namespace allows for parameterized creation of a depreciation schedule
View excel-lambda-depn.txt
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
ncalm / excel-lambda-namespace-LIST.txt
Created Aug 10, 2022
This Excel Lambda namepace contains several functions for working with lists
View excel-lambda-namespace-LIST.txt
ROW = LAMBDA(row,
LAMBDA(array,INDEX(array,row,))
);
/*
Gets the first row from a list
*/
FIRST = LAMBDA(array,
list.ROW(1)(array)
);
@ncalm
ncalm / excel-lambda-namespace-TEXT.txt
Created Aug 10, 2022
This Excel Lambda namespace contains several functions for working with text
View excel-lambda-namespace-TEXT.txt
/*
Gets all substrings of exactly length n from string
*/
SUBSTRINGS = LAMBDA(length,
LAMBDA(string,
LET(
substrings,MID(string,SEQUENCE(LEN(string)),length),
FILTER(substrings,LEN(substrings)=length)
)
)