tonmcg

let Table.GetDummies = 
    (sourceTable as table, columnName as text) as table =>
    let
        distinctTable = 
            let 
                source = Table.AddKey(Table.Distinct(Table.FromList(Table.Column(sourceTable, columnName), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),{"Column1"},false),
                addIndex = Table.AddIndexColumn(source, "index", 0, 1)
            in
                addIndex,
        origTable = 

tonmcg

let 
    Table.CreateAmortization = let 
        AmortizationFunction = (initialAmount as number, interestRate as number, numberOfPeriods as number, periodicity as text, optional date as any, optional balloonPayment as number) as table =>
        let
            CompoundingMap = {{"Daily",365}, {"Monthly",12}, {"Quarterly",4}, {"Semi-annual",2}, {"Annual",1}},
            CompoundingPeriod = List.First(List.Select(CompoundingMap, each _{0} = periodicity)){1},
            Balloon = if balloonPayment is null then 0 else balloonPayment,
            PeriodicInterest = interestRate / CompoundingPeriod,
            TotalPeriods = numberOfPeriods * CompoundingPeriod,
            Annuity = initialAmount * ((PeriodicInterest) * Number.Power(1 + (PeriodicInterest), TotalPeriods)) / (Number.Power(1 + (PeriodicInterest), TotalPeriods) - 1) - Balloon * (-PeriodicInterest) / ((1 + PeriodicInterest) - (Number.Power(1 + PeriodicInterest, TotalPeriods + 1))),

tonmcg

let 
    List.MinMaxScaler = (initialList as list, optional outputRange as list) as list =>
        let
            featureRange = if outputRange is null then {0,1} else outputRange,
            list = List.Buffer(initialList),
            count = List.Count(list),
            min = List.Min(list),
            max = List.Max(list),
            a = featureRange{0},
            b = featureRange{1},

tonmcg

let 
    List.GenerateRandom = (sourceList as list, numberSelections as number) as list =>
        let
            list = List.Buffer(sourceList),
            count = List.Count(list),
            Selection = List.Generate(
                () => 1, 
                each _ <= numberSelections, 
                each _ + 1,
                each list{Number.RoundDown(

tonmcg

let 
    List.RandomSelection = (sourceList as list) as text =>
        let
            Selection = sourceList{Number.RoundDown(Number.RandomBetween(0, List.Count(sourceList)))}
        in
            Selection,

    DefineDocs = [
        Documentation.Name =  " List.RandomSelection",
        Documentation.Description = " Randomly select a value from a list.",

tonmcg

let 
    List.StandardScaler = (initialList as list) as list =>
        let
            list = List.Buffer(initialList),
            count = List.Count(list),
            sd = List.StandardDeviation(list),
            m = List.Average(list),
            Scaler = List.Generate(
                ()=> [n = 0],
                each [n] < count,

tonmcg

// adapted from Mark White's blog
// http://sql10.blogspot.sg/2016/06/reading-zip-files-in-powerquery-m.html
let 
    Table.UnzipContents = (ZIPFile) =>
        let
            Header = BinaryFormat.Record([
                MiscHeader = BinaryFormat.Binary(14),
                BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),

tonmcg

let 
    Census.GetStates = let 
        StatesFunction = (optional includeTerritories as logical) as table =>
        let
            // load custom GetTables function from Github Gist
            GetTables = Expression.Evaluate(
                Text.FromBinary(Web.Contents("http://bit.ly/HtmlGetTables")),
                [
                    #"Table.SelectRows" = Table.SelectRows,
                    #"Text.BetweenDelimiters" = Text.BetweenDelimiters,

tonmcg

let 
    Census.GetGeographies = let 
        GeographiesFunction = (year as text, geography as text) => 
        let
            UnzipContents = Expression.Evaluate(
                Text.FromBinary(Web.Contents("http://bit.ly/UnzipContents")),
                [
                    #"Binary.Buffer" = Binary.Buffer,
                    #"Binary.Decompress" = Binary.Decompress,
                    #"BinaryFormat.Binary" = BinaryFormat.Binary,

tonmcg

// adapted from  Chris Koester's blog
// for an example, see tonymcgovern.com/powerquery/twitter/get-token/
// for more details, see https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/ 
let
    Twitter.GetOAuthToken = (consumerKey as text, consumerSecret as text) as text =>
    let
        // Concatenates the Consumer Key & Consumer Secret and converts to base64
        authKey = "Basic " & Binary.ToText(Text.ToBinary(consumerKey & ":" & consumerSecret),0),
        url = "https://api.twitter.com/oauth2/token",
        // Uses the Twitter POST oauth2/token method to obtain a bearer token