ncalm

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

(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

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

(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

# 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

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

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

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

/*
Name: ADD

Description: Adds two numbers

Parameters:
- a: a number
- b: also a number
*/
ADD = LAMBDA(a, b, a + b);

ncalm

(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)},