How to use List.Generate to make API Calls in Power Query M

The List.Generate function allows you to create a series of values based on logic. What makes List.Generate so powerful is that it can perform a certain action until the provided condition returns false. This principle allows you to call an API, and keep firing requests until you reach the last record in the database. It is similar to a do-while loop, applying a function as long as a condition holds true. So how can you do that?

This post shows how you can retrieve data from the PokeAPI. Imke Feldman used this same API and showed a way to retrieve API data up to the last page. This post is more in depth and shows two different approaches.

Making an API Call

Imagine there’s a limit to the amount of records you can retrieve per API call. In each request you can retrieve up to 100 records, and then move on to the next 100, and then the next, etc. You can give those instructions by using the limit and offset parameters in the API request. The first 4 requests look like:

https://pokeapi.co/api/v2/pokemon?limit=100&offset=0
https://pokeapi.co/api/v2/pokemon?limit=100&offset=100
https://pokeapi.co/api/v2/pokemon?limit=100&offset=200
https://pokeapi.co/api/v2/pokemon?limit=100&offset=300

The limit value stays the same for each request, whereas the offset increments with 100 each call. This requirement is very similar to using List.Generate for pagination, but in bigger steps.

So how can we import the data of the first request into Power Query? You can start by creating a New Query, and choose From Web. Then paste the first URL into the address bar.

List.Generate in Power Query

The request retrieves a JSON file. Power Query automatically recognizes the data format and adds the Json.Document function to the formula.

Record from Json file

The Json file shows as a record in Power Query. From this record you only need the data from the results column. You can return the list values by referencing the column name behind the formula.

Check out my complete guide to lists to learn more on how referencing columns returns a list.

// Notice the [result] at the end of below code
= Json.Document( Web.Contents(
"https://pokeapi.co/api/v2/pokemon?limit=100&offset=0"))[results]

You can remove this column reference again, but it’s good to remember the effect of referencing a column name. We will use this later in this exercise.

Create Function for API Call

For the API calls you will need transform the code into a function. List.Generate can then call that function multiple times.

To do that start with the previous code. When you open the advanced editor, the code generated by the From Web operation looks like:

let
  Source = 
    Json.Document(
      Web.Contents(
        "https://pokeapi.co/api/v2/pokemon?limit=100&offset=0"
     )
   )
in
    Source

You want to use this code and apply it to a set of offsets. Since each request will have a different offset, you need a variable here. And to do that you can best use a function.

To turn this code into a function you:

  • open the advanced editor and add the required variable(s). In this case you need a variable for offset. To do that, input the name between parenthesis and end the first line with =>.
  • Next you want to replace the offset value with the variable name. For as Power Query expects a text value, make sure to transform the variable to text by using Number.ToText.
(offset) =>
let
   Source = 
     Json.Document(
       Web.Contents(
         "https://pokeapi.co/api/v2/pokemon?limit=100&offset=" 
            & Number.ToText( offset )
      ) 
    )
in
    Source

This creates your new function. Rename the function to GetPokemon.

Create Do-While Loop with List.Generate

With the API function prepared, all that’s left is to use the List.Generate function to retrieve the data. The function needs to add increments of 100 to the offset variable of the formula.

Let’s first see a basic example on generating a list of values. List.Generate can create a series of values that increase by 100 in each step by writing:

// Returns {0, 100, 200}
= List.Generate(   
     ()  =>  0,        // starting value is 0
     each _ <= 200,    // as long as the generated values <= 200
     each _ + 100      // increase each step with 100
)

We are making a do-while loop that runs as long as the generated value is less than or equal to 200. The loop starts with the value 0.

If this went to quickly, you can read more examples on List.Generate in Power Query.

Return Records in List.Generate

The List.Generate function does not only take scalar values as input but can also take structured values. Structured values could be values like lists, tables and records.

Your requirement is to create the list of offset values, but to also return the values from the API call. The easiest way to do that is to return two columns in a record. You can transform previous statement into a list of records by writing:

// Returns {[Offset = 0], [Offset = 100], [Offset = 200]}
= List.Generate(   ()  => [Offset = 0 ],  
   each [Offset] <= 200,                 
   each [ Offset = [Offset] + 100 ] )

This example showed a record with a single value, but you can add another value to it by writing:

// Returns the following record
// {[Offset = 0, Pokemon = "a"], [Offset = 100, Pokemon = "a"], 
//  [Offset = 200, Pokemon = "a"]}
= List.Generate(   
   ()  => [Offset = 0, Pokemon = "a" ], 
   each [Offset] <= 200  ,  
   each [ Offset = [Offset] + 100, 
          Pokemon = [Pokemon] ] )

If you then only want to return the list of values from the [Pokemon] column in the record, you can write:

// Returns {[Pokemon = "a"],Pokemon = "a"], [Pokemon = "a"]}
= List.Generate(   
   ()  => [Offset = 0, Pokemon = "a" ], 
   each [Offset] <= 200  ,  
   each [ Offset = [Offset] + 100, 
          Pokemon = [Pokemon] ], 
   each [Pokemon] )

The benefit of that is that you can incorporate both variables in your calculations, but only return one for the result.

You now know how to build up a List.Generate formula that takes multiple variables as an argument and returns a single variable. With that you have all building blocks for the formula that makes multiple API calls.

Multiple API Calls with List.Generate

Your next step is to incorporate the GetPokemon function into the List.Generate formula. You can start with the previous code and make the following adjustments:

  • Instead of returning a value for the Pokemon variable, enter the GetPokemon function. Provide the function with the value 0. This is the offset value that is used in the first request.
  • In the third argument, indicate that the Pokemon variable should take the offset value and apply it to the GetPokemon function.
= List.Generate(   
     ()  =>  [ Offset = 0, Pokemon = GetPokemon(0) ], 
     each [Offset] <= 200,  
     each [ Pokemon = GetPokemon( [Offset] ),
            Offset =  [Offset] + 100 ],  
     each [Pokemon]
 )

Now instead of returning the entire record on line 6, you can return the list values by referencing the column name results.

= List.Generate(   
     ()  =>  [ Offset = 0, Pokemon = GetPokemon(0) ], 
     each [Offset] <= 200,  
     each [ Pokemon = GetPokemon( [Offset] ),
            Offset =  [Offset] + 100 ],  
     each [Pokemon][results] 
 )

So far we hardcoded the amount of times the function was called. Since you want to make API calls until reaching the end of the database, you can check whether the List value is empty. Once there are no more records Power Query will return an empty list.

At that point, List.Generate should stop making the calls. You can use the List.IsEmpty function to give those instructions.

= List.Generate(   
     ()  =>  [ Offset = 0, Pokemon = GetPokemon(0) ], 
     each not ( List.IsEmpty( [Pokemon][results] ) ) ,  
     each [ Pokemon = GetPokemon( [Offset] ),
            Offset =  [Offset] + 100 ],  
     each [Pokemon][results] 
 )

With the above you called a function multiple times in List.Generate and looped through all records until reaching the end of the dataset.

The result is a List containing Lists with Records. What’s left is to dive into the data and return the pokemon in the database.

  • In the Transform tab, select To Table to transform the list into a table.
  • In that table click on the two opposite-facing arrows and select Expand to new Rows
  • Click those arrows once more and select only the name column.

Alternative Solution

Previous example checked whether the retrieved list had any values in it. An alternative is to use the next column in the record. When the next record does not contain any values, the column returns null.

= List.Generate( () => [ Offset = 0, Pokemon = GetPokemon( 0 ) ], 
                  each [Pokemon][next] <> null,  
                  each [ Pokemon = GetGet( [Offset] ),
                         Offset = [Offset] + 100 ],  
                  each [Pokemon][results] 
  )

And that wraps everything up. Did you come up with a different solution? Or did I miss out on something important? Let me know in the comments. And if List.Generate still has secrets for you, make sure to check out more examples in my foundational post on using List.Generate in Power Query.

Enjoy Power Query!

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

2 thoughts on “How to use List.Generate to make API Calls in Power Query M”

  1. Nice blog about less explored area of Power Query, thanks for sharing the wealth. Please keep it coming. Couple of questions:
    Can PQ call be authenticated, and
    Can it be POST if I want to push my data into the source?

    Reply
    • Thanks Vlad, appreciate you taking the effort to comment. It’s a fun area to delve into!

      About your question. I’m not completely sure a call can be authenticated. I think if it requests credentials you can provide credentials.

      Yet my experience with API call is as little as this post. This was a first time. If I had an API we could test this with that would be fun.

      Best,
      Rick

      Reply

Leave a comment