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

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

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 GetTables =
    (url as text) =>
    let
        DOM = Text.FromBinary(Web.Contents(url)),
        DOCTYPE = 
            let
                DOCTag = "<!" & Text.BetweenDelimiters(DOM, "<!", ">") & ">"
            in
                DOCTag,
        HTMLOpeningTag = 

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

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