Skip to content

Instantly share code, notes, and snippets.

hitting computer with hammer

Owen Price ncalm

hitting computer with hammer
View GitHub Profile
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
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.
func =
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 / 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) =>
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 / 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 / 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
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.
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
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 = "";
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
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
Gets the first row from a list
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