Skip to content

Instantly share code, notes, and snippets.

Jay Killeen jaykilleen

Block or report user

Report or block jaykilleen

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View pquery.m
(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
View Text.Count.m
(Haystack as text, Needle as text) as number =>
List.Count(Text.Split(Haystack, Needle)) - 1
@jaykilleen
jaykilleen / australian-postcodes.m
Last active Dec 20, 2018 — forked from randomecho/australian-postcodes.sql
Australian postcodes (with states and suburb names) geocoded with latitude and longitude.
View australian-postcodes.m
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(
{
@jaykilleen
jaykilleen / init
Last active Aug 29, 2015
Excel VBA Basic Initialization module
View init
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 Mar 19, 2015
Excel VBA Advanced Find Unique Values from Table to separate worksheet
View init
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 Aug 29, 2015
Excel VBA 'functions' module
View gist:892d0f0eed87b9f8e6b0
'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 Aug 29, 2015
Excel VbA to find a value in an array. Use as find_in_arry
View gist:66aa778a315495819c52
'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 Jun 9, 2016
Excel VBA to get the row number where a value is found. Use as get_row_number
View get_row_number.txt
'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 Aug 29, 2015
Excel VBA to convert column to number. Use as get_column_number
View gist:294d41c3b774bc88f82d
'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 Aug 29, 2015
Excel VBA Find Last Row as get_last_row function
View gist:cce6afd13d2bd2a8c70b
'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
You can’t perform that action at this time.