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

Have you ever wanted to make API calls directly in Power Query? Well, you’re in luck! In this article, I’m going to show you how to use the powerful List.Generate function to make API calls in Power Query.

We’ll start by making an API call, then we’ll create a function using List.Generate to return the relevant fields. With this technique, you’ll be able to easily access data directly from an API, giving you some options to experiment with it.

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. I’ll make sure to explain everything in a simple and easy-to-understand way, so even if you’re a beginner, you’ll be able to follow along.

Table of contents

• List.Generate
• Make API Call
• Create Function for API Call
• Do-While Loop
• Return Specific Records
• Multiple API Calls
• Alternative Solution
• Conclusion

List.Generate

To get started with APIs, we will use the List.Generate function. List.Generate allows you to create a series of values based on logic. If you’ve never worked with List.Generate check out this article.

What makes List.Generate so powerful 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?

Make 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 to 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.

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

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 to 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 this:

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. As Power Query expects a text value, transform the variable to text 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.

Do-While Loop

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 of generating a list of values. List.Generate can create a series of values that increase by 100 in each step by writing:

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

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.

Return Specific Records

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 also to return the values from the API call. The easiest way to do that is to return two columns in a record. You can transform the previous statement into a list of records by writing:

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

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

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

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

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

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 the building blocks for the formula that makes multiple API calls.

Multiple API Calls

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.
• The third argument indicates that the Pokemon variable should take the offset value and apply it to the GetPokemon function.

= List.Generate( ()  =>  [ Offset = 100, 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 = 100, 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 = 100, 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

The 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 = 100, Pokemon = GetPokemon( 0 ) ], 
                  each [Pokemon][next] <> null,  
                  each [ Pokemon = GetPokemon( [Offset] ), Offset = [Offset] + 100 ],  
                  each [Pokemon][results] )

Conclusion

And that wraps everything up! We’ve covered everything you need to know about how to use the List.Generate function to make API calls in Power Query. From making an API call, to creating a function that returns the relevant fields, you should now have a solid understanding of how to use this powerful function to access API data.

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.

Happy Querying!