Multiple API Calls as Source

[a68tbird]

Hello All,

   I am extracting geographical and event information from a third-party API call into PowerBI. The challenge here is that I have over a hundred calls to make in order to get my full dataset. I was hoping that there might be a way that I could invoke an included function to make each of the calls in the same way one might invoke a function to parse through all Excel files in a folder (I've done this many times).

I have all of the API URL calls in a spreadsheet.  Is there someway that I could "read" these URLs in my M code? My function works fine:

(varFilePath) =>
let
Source = Xml.Tables(Web.Contents(varFilePath)),
Table1 = Source{1}[Table],
Table0 = Table1{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:Name", type text}, {"Attribute:Address1", type text}, {"Attribute:Address2", type text}, {"Attribute:Zip", type text}, {"Attribute:CityID", Int64.Type}, {"Attribute:PhoneAreaCode", Int64.Type}, {"Attribute:PhoneNumber", type text}})
in
#"Changed Type"

Is there a way that I can use my spreadsheet as a source for my source, if you will :). 

Thanks in advance!

[v-chuncz-msft · Accepted solution]

@a68tbird,

You may just select Invoke Custom Function in Query Editor.

http://community.powerbi.com/t5/Desktop/Create-tables-based-on-all-Query-Parameters/td-p/345947

[ericOnline]

Hello @v-chuncz-msft. If I could as a follow-on question here...

What about POST requests that use JSON rather than a URL parameter? How can I pass key : value pairs from a table rather than 1 by 1?

Here is my example API call (works great):

let
    url = "https://company.com/api/path",
    headers = [
        "apikey" = thisisnotreallymyapikey
        "accept" = application/json,
        "Content-Type" = application/json
    ],
    content = "{
        ""key"": {
            ""nestedKey"": value,
            ""nestedKey"": value
        },
        ""key"": """",
        ""key"": ""value"",
        ""key"": ""value"",
        ""key"": value,
        ""key"": [
            {""key"": ""value"", ""key"": ""value""}
        ]
    }",
    webdata = Web.Contents(url, [Headers=headers,Content = Text.ToBinary(content)]),
    response = Json.Document(webdata)
in
    response

[a68tbird]

Oh! That's perfect! Thanks very much.