Forum Discussion

a68tbird's avatar
a68tbird
Resolver II
8 years ago
Solved

Multiple API Calls as Source

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!

 

 

Need Help

3 Replies

    • ericOnline's avatar
      ericOnline
      Post Patron
      7 years ago

      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