Using API to extract data into Power BI

[Anonymous]

Hi there, 

I am working on a project for a client to develop some Power BI reports based on their data using an API connection. Having never used an API connection, I need some help as I fear I am barking up the wrong tree. 

What I did was to use the Web connector in Power BI. I set the connection up as follows (fake website but shows what I did):

The resultant code is shown below:

let
Source = Json.Document(Web.Contents("https://io.website.com/v1/account", [Headers=[#"X-API-Token"="MYTOKENDETAILS", #"X-Integration-ID"="rre5b7rg-a0ss-4cfd-8311-1e84d0d23359", #"Content-Type"="application/json", Accept="application/json", Method="Get"]])),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"

However, what I get back is just 1 piece of code:

The instructions from the client were:

1. Query the /v1/records endpoint which returns the basic record data.
2. You’ll need to take the “user_id” and “record_id” to make another request to get the record’s full field data.
3. Query /v1/records/{user_id}/{record_id}

I have spent quite some time looking around for how to extract this data and watched videos on using PowerShell (which is a whole new subject on its own). I am worried I am going down a rabbit hole and would appreciate if anyone can point me in the right direction! 

Thanks for any suggestions. 

[lbendlin · Accepted solution]

You can stay within Power Query and make subsequent Web.Contents calls. You can use List.Generate(), List.Accumulate(), recursive functions, or data from your initial call to set up the looping.  In your case you may not even need loops, only data from the original request to formulate the second.

By the way,  Method ="GET" is not a functionality of Web.Contents. You change methods via the payload.

Web.Contents - PowerQuery M | Microsoft Docs