Trial #29: Iterating Over Multiple API Endpoints in a PowerBI Query

Problem:

It is straight forward to source data from a web request in PowerBI. You can do so by selecting Get Data -> Web.

Optionally, under advanced you can “Add Parts” to your URL. For example, the code behind below, found in the advanced editor, is generated by the URL parts https://craigchamberlain.github.io/moon-data/api/moon-phase-data/ and 1700.

let
  Source = Json.Document(
    Web.Contents(
      "https://craigchamberlain.github.io/moon-data/api/moon-phase-data/" & 
      "1700"
    )
  )
in
  Source

However, this will return just one response. If you need to iterate over paged results or indeed target a different URL entirely.

Solution:

The first step might be to converting the query into a function. For those familiar with Expression Bodied Functions or Lambda Expressions the syntax may be familiar, simply prefix the expression with zero or more comma separated parameters in parenthesis, followed by =>.

Please note if you may need to convert the parameter type to Text, in my example from a Number. I will write another post on writing parameter types.

(year) =>
let
  Source = Json.Document(
    Web.Contents(
     "https://craigchamberlain.github.io/moon-data/api/moon-phase-data/" & 
      Number.ToText(year)
    )
  )
in
  Source

The final step at this stage would be to iterate over the various years and collect into a unified dataset.

let

  // Create a list of numbers you with to iterate over
  Min = 1700,
  Max = 2082,
  Years = List.Numbers(Min, Max - Min + 1), 
   
  // Map result of your function call to the list of items
  AllYearsData = List.Transform(Years, (year) =>
    Json.Document(
      Web.Contents(
        "https://craigchamberlain.github.io/moon-data/api/moon-phase-data/" & 
        Number.ToText(year)
      )
    )
  )

in
    AllYearsData

Please note that this is not how I would present this query in production. At a minimum, I would define the function in a separate file. I would also prefer to see the upper and lower limits of the list provided by variables, or the range by a query and the parameters types defined. There would be further transformations to make the data usable but this is easily achieved in the GUI and is not within the scope of this particular trial.