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-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 =>
let
// Handle omitted IgnoreCase with default false
_IgnoreCase = if IgnoreCase = null then false else IgnoreCase,
@ncalm
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 =>
let
AddRecords = Table.AddColumn(Data, "Record",each _),
AddSortedComparisonValues = Table.AddColumn(
AddRecords,
"SortedComparisonValues",
each List.Sort(
Record.FieldValues(
Record.SelectFields(_, ComparisonColumns)
@ncalm
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
let
/*
An example of how to retrieve CKAN resources from openfinance.houstontx.gov
*/
// URL to get a list of available packages in this CKAN instance
package_list_url = "https://openfinance.houstontx.gov/api/3/action/package_list",
// URL to get the package metadata e.g. id=budget-2019
@ncalm
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) =>
let
url = "http://quickstats.nass.usda.gov/api/get_param_values/?",
query = "param=" & param_name,
Source = Json.Document(
Web.Contents(url & query & "&format=JSON",
[ApiKeyName="key"])
),
@ncalm
ncalm / python-list-slicing-examples.py
Created May 2, 2023 23:39
Random examples of list slicing in Python
View python-list-slicing-examples.py
# 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',
'Pear','Pineapple',
'Grape','Berry']
@ncalm
ncalm / excelbi-pq68-nocomments.py
Last active April 9, 2023 15:33
Python solution to Excel BI PQ68 challenge
View excelbi-pq68-nocomments.py
import pandas as pd
import calendar
url_raw = 'https://onedrive.live.com/view.aspx?resid=E11B26EEAACB7947!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
ncalm / excelbi-pq67.py
Created April 8, 2023 15:24
Excel BI PQ67 in Python
View excelbi-pq67.py
import pandas as pd
url = 'https://onedrive.live.com/download?resid=E11B26EEAACB7947!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
ncalm / excelbi-excel-challenge-168.py
Created April 7, 2023 14:10
Python solution to Excel challenge 168
View excelbi-excel-challenge-168.py
import pandas as pd
# Original link: https://onedrive.live.com/view.aspx?resid=E11B26EEAACB7947!8211&ithint=file%2cxlsx&authkey=!ABt7knDc8bHLlFU
problem_url = 'https://onedrive.live.com/download?resid=E11B26EEAACB7947!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
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
Parameters:
- a: a number
- b: also a number
*/
ADD = LAMBDA(a, b, a + b);
@ncalm
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 =>
let
endpoint_function
= (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)},