tonmcg

let 
    GitHub.GetGists = (userName as text) as table =>
    let
        Source = Json.Document(Web.Contents("https://api.github.com/users/" & userName & "/gists")),
        count = List.Count(Source),
        fileTableType = type table [id = text, filename= text, type = text, language = text, raw_url = text, size= Int64.Type],
        metaTableType = type table [url = text, forks_url = text, commits_url = text, id = text, git_pull_url = text, git_push_url = text, html_url = text, public = logical, created_at = datetime, updated_at = datetime, description = text, comments = Int64.Type, user = text, comments_url = text, truncated = logical],
        gistsMetaRecords = Table.FromList(Source, Splitter.SplitByNothing(), {"meta"}, null, ExtraValues.Error),
        gistsMetaTable = Table.ExpandRecordColumn(gistsMetaRecords, "meta", {"url", "forks_url", "commits_url", "id", "git_pull_url", "git_push_url", "html_url", "public", "created_at", "updated_at", "description", "comments", "user", "comments_url", "truncated

tonmcg

// from NIST 7.2.6.2 Percentiles
// https://www.itl.nist.gov/div898/handbook/prc/section2/prc262.htm
// Note that there are other ways of calculating percentiles in common use
// Hyndman and Fan (1996) in an American Statistician article evaluated nine different methods (R1 to R9)
// for computing percentiles relative to six desirable properties. 
// Their goal was to advocate a "standard" definition for percentiles that would be implemented in statistical software. 
// Although this has not in fact happened, most statistical and spreadsheet software use one of the methods described in Hyndman and Fan.
// The method used here is patterned after the R6, R7, and R8 methods; R7 is the default method used in Excel and R and thus the default for this function
let 
    List.Percentile = (sourceList as list, p as number, optional givenMethod as number) as number =>

tonmcg

let
    Twitter.SearchTweets = (
        q as text, 
        optional n as number,
        optional result_type as text,
        optional geocode as text,
        optional lang as text,
        optional locale as text,
        optional until as text,
        optional since_id as text,

tonmcg

let
    Twitter.GetFriends = (screen_name as text, number as number, optional cursor as text) as any =>
        let
            initial_cursor = if cursor is null then "-1" else cursor,
            count = if number is null then 5000 else number,
            max_requests = 15, // Twitter-provided max for app authorized calls
            number_requests = if count > 75000 then max_requests else Number.RoundUp(count / 15), // 75,000 is the maximum number of items that can be returned in a 15-minute period
            baseUri = "https://api.twitter.com",
            relativeUri = "1.1/friends/ids.json",
            fullUri = Uri.Combine(baseUri,relativeUri),

tonmcg

let
    Twitter.LookupUsers = (user_ids as text) as any =>
        let
            id_list = Text.Split(user_ids, ","),
            id_count = List.Count(id_list),
            max_requests = 300, // Twitter-provided max for app authorized calls
            number_requests = if id_count > 30000 then max_requests else Number.RoundUp(id_count / 100), // 30,000 is the maximum number of items that can be returned in a 15-minute period
            //id_count = Text.Length(user_ids) - Text.Length(Text.Replace(user_ids,",","")) + 1,
            baseUri = "https://api.twitter.com",
            relativeUri = "1.1/users/lookup.json",

tonmcg

let
    Twitter.GetFollowers = (screen_name as text, number as number, optional cursor as text) as any =>
        let
            initial_cursor = if cursor is null then "-1" else cursor,
            count = if number is null then 5000 else number,
            max_requests = 15, // Twitter-provided max for app authorized calls
            number_requests = if count > 75000 then max_requests else Number.RoundUp(count / 15), // 75,000 is the maximum number of items that can be returned in a 15-minute period
            baseUri = "https://api.twitter.com",
            relativeUri = "1.1/followers/ids.json",
            fullUri = Uri.Combine(baseUri,relativeUri),

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

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

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

// 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),