View powerquery-fnSelectColumnNames.m
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
View powerquery-fnCleanNwiseMatch.m
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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) |
View powerquery-ckan-GetCKANResources.m
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
View USDAQuickStats-GetParameterAllowedValues.m
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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"]) | |
), |
View python-list-slicing-examples.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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'] |
View excelbi-pq68-nocomments.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 = {} |
View excelbi-pq67.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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']] |
View excelbi-excel-challenge-168.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
View excel-lambda-function-ADD.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Name: ADD | |
Description: Adds two numbers | |
Parameters: | |
- a: a number | |
- b: also a number | |
*/ | |
ADD = LAMBDA(a, b, a + b); |
View powerquery-DateList.m
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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)}, |
NewerOlder