Skip to content

Instantly share code, notes, and snippets.

View jaykilleen's full-sized avatar

Jay Killeen jaykilleen

View GitHub Profile
(Haystack as text, Needle as text) as number =>
List.Count(Text.Split(Haystack, Needle)) - 1
(Rec as record, Lambda as function) as record =>
let
Keys = Record.FieldNames(Rec),
Values = Record.FieldValues(Rec),
Renamed = List.Transform(Keys, each Lambda(_, Record.Field(Rec, _))),
Recorded = Record.FromList(Values, Renamed),
Return = Recorded
(Haystack as text, Needle as text) as number =>
List.Count(Text.Split(Haystack, Needle)) - 1
@jaykilleen
jaykilleen / australian-postcodes.m
Last active December 20, 2018 02:56 — forked from randomecho/australian-postcodes.sql
Australian postcodes (with states and suburb names) geocoded with latitude and longitude.
This file has been truncated, but you can view the full file.
/*
Taken and cribbed from blog.datalicious.com/free-download-all-australian-postcodes-geocod
May contain errors where latitude and longitude are off. Use at own non-validated risk.
*/
let
Source = Table.FromList(
{
[ID="200",Name="Australian National University",State="ACT",Lat="-35.28",Long="149.12"],
@jaykilleen
jaykilleen / init
Last active August 29, 2015 14:17
Excel VBA Basic Initialization module
Option Explicit
'Call this method from your main VBA module using 'Call init' or 'Call init.init' (if placed in a module called 'init')
'Declare the activeworkbook and any other related workbooks required
Public wb_1 As Workbook 'this is your activeworkbook where this VBA will be hosted
Public wb_2 As Workbook
'Delcare the worksheets that you will be interactive with
Public ws_1 As Worksheet
@jaykilleen
jaykilleen / init
Created March 19, 2015 01:12
Excel VBA Advanced Find Unique Values from Table to separate worksheet
Public ws_unique As Worksheet
Public ws_copy_to As Worksheet
Public ws_table1 As Worksheet
Public lr_table1 As Long
Public lr_unique As Long
Sub init()
Set ws_unique = Sheets("unique")
Set ws_copy_to = Sheets("copy_to")
@jaykilleen
jaykilleen / gist:892d0f0eed87b9f8e6b0
Last active August 29, 2015 14:17
Excel VBA 'functions' module
'copy the content of this gist into a new module in VBA (get to VBA by pressing Alt+F11)
'you then have access to the functions below from anywhere else in your workbook VBA scripts
'these scrips can be found at Gist on Github at https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0
Option Explicit
Function get_last_row(sheetname As String, column_number As Integer) As Long
get_last_row = Sheets(sheetname).Cells(Rows.Count, column_number).End(xlUp).row
@jaykilleen
jaykilleen / gist:66aa778a315495819c52
Last active August 29, 2015 14:17
Excel VbA to find a value in an array. Use as find_in_arry
'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `find_in_array("dummy text", some_array)
'Where some_array might be something like ["camp","light bulb", "dummy text")
'This function will return TRUE if it finds the value in the array
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module
Function find_in_array(FindMe As String, myArray() As String)
Dim C As String
@jaykilleen
jaykilleen / get_row_number.txt
Last active June 9, 2016 05:42
Excel VBA to get the row number where a value is found. Use as get_row_number
'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `get_row_number("dummy text", 1)
'The integer '1' will tell this to do a ctrl+f on column A
'When it finds the first value it will return the row number of that value
'Best used on columns that contain unique values
'You might need to change the xlPart to xlWhole if you are going for an exact match
'You could also extend this function to pass in a boolean to look for whole or partial value and replace xlPart with the value passed in
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.
@jaykilleen
jaykilleen / gist:294d41c3b774bc88f82d
Last active August 29, 2015 14:17
Excel VBA to convert column to number. Use as get_column_number
'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `get_column_number("header column name", 15, TRUE)
'This will look in row 1 (usually where your headers are on a table) for a value and return the column number
'If your headers start on a different row (ie you have slicers above your table) you can pass in the row number
`This will match a partial word by default, pass in TRUE if you want to match by the whole word
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.
Function get_column_number(sheet As Worksheet, value As String, Optional row As Variant, Optional whole As Boolean) As Integer
@jaykilleen
jaykilleen / gist:cce6afd13d2bd2a8c70b
Last active August 29, 2015 14:17
Excel VBA Find Last Row as get_last_row function
'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `get_last_row(ThisWorkbook.Sheets("sheet_1"), 1)
'It will then find you the last row of column A
`Change the interger for the second argument to use any other column
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.
Option Explicit
Function get_last_row(sheetname As String, column_number As Integer) As Long