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-fnSelectColumnNames.m
Created May 31, 2023 18:51
Power Query function for selecting a subset of column names from a table using an arbitrary Text Membership function
View powerquery-fnSelectColumnNames.m
let fn =
Table as table,
MembershipFunction as function,
Substring as text,
optional IgnoreCase as nullable logical
) as list =>
// Handle omitted IgnoreCase with default false
_IgnoreCase = if IgnoreCase = null then false else IgnoreCase,
ncalm / powerquery-fnCleanNwiseMatch.m
Created May 23, 2023 16:05
Power Query function for cleaning pairwise matches on an arbitrary column-set
View powerquery-fnCleanNwiseMatch.m
(Data as table, ComparisonColumns as list, ReplacementFunction as function) as table =>
AddRecords = Table.AddColumn(Data, "Record",each _),
AddSortedComparisonValues = Table.AddColumn(
each List.Sort(
Record.SelectFields(_, ComparisonColumns)
ncalm / powerquery-ckan-GetCKANResources.m
Created May 19, 2023 15:43
Power Query Code for working with CKAN (example is Houston City Council openfinance)
View powerquery-ckan-GetCKANResources.m
An example of how to retrieve CKAN resources from
// URL to get a list of available packages in this CKAN instance
package_list_url = "",
// URL to get the package metadata e.g. id=budget-2019
ncalm / USDAQuickStats-GetParameterAllowedValues.m
Last active May 16, 2023 21:33
A function for retrieving data from the USDA Quick Stats API
View USDAQuickStats-GetParameterAllowedValues.m
(param_name as text) =>
url = "",
query = "param=" & param_name,
Source = Json.Document(
Web.Contents(url & query & "&format=JSON",
ncalm /
Created May 2, 2023 23:39
Random examples of list slicing in Python
# Lists are zero-indexed
# We can provide three-part slices as list[start:stop:step]
# If start is omitted, start at the first item
# If stop is omitted, stop at the last item
# If step is omitted, step by 1
fruits = ['Apple','Orange',
ncalm /
Last active April 9, 2023 15:33
Python solution to Excel BI PQ68 challenge
import pandas as pd
import calendar
url_raw = '!8228&ithint=file%2cxlsx&authkey=!AOypSEkMdUjdBN0'
url = url_raw.replace('view.aspx','download')
df = pd.read_excel(url, sheet_name = 'Sheet1')
def combine_dicts(dicts):
result = {}
ncalm /
Created April 8, 2023 15:24
Excel BI PQ67 in Python
import pandas as pd
url = '!8221&ithint=file%2cxlsx&authkey=!AB0T-fSTNVawR3g'
df = pd.read_excel(url, sheet_name = 'Sheet1')
# fills down a column in a dataframe with the first non-blank value above
# used for House and Name, so defined as a function here
fill_down = lambda column : lambda df: df[column].ffill()
df_pivot = (df[['Data1','Data2']]
ncalm /
Created April 7, 2023 14:10
Python solution to Excel challenge 168
import pandas as pd
# Original link:!8211&ithint=file%2cxlsx&authkey=!ABt7knDc8bHLlFU
problem_url = '!8211&ithint=file%2cxlsx&authkey=!ABt7knDc8bHLlFU'
df = pd.read_excel(problem_url, sheet_name = 'Sheet1')
# Get the indices of the capital letters
# list(w) splits each word into characters
# enumerate lets us loop through each element c in its list parameter and provides an index i
ncalm / excel-lambda-function-ADD.txt
Last active February 15, 2023 17:33
This lambda function adds two numbers together
View excel-lambda-function-ADD.txt
Name: ADD
Description: Adds two numbers
- a: a number
- b: also a number
ADD = LAMBDA(a, b, a + b);
ncalm / powerquery-DateList.m
Created February 6, 2023 16:01
Custom Power Query function for creating lists of dates between two endpoints
View powerquery-DateList.m
(from as date, to as date, step_function as function) as list =>
= (step_function as function) as function =>
(date as date) as number =>
let lookup =
{Date.AddDays, Number.From(date)},
{Date.AddMonths, Date.Year(date)*12 + Date.Month(date)},
{Date.AddQuarters, Date.Year(date)*4 + Date.QuarterOfYear(date)},