Benjamin Ejzenberg recently shared something that caught my attention: "APIs are not reserved for developers." He also framed it in the simplest possible way: think of an API like a restaurant server. You place an order, the server relays it to the kitchen, then comes back with your dish. In his words, the API is that server, connecting you to a remote database and bringing back the information you requested.

That idea resonates because it removes the intimidation factor. If you can write a Power Query query, you can call an API. And once you do, a whole category of data work becomes faster: less downloading files, less copy-pasting from websites, and more automated refreshable tables.

In this post, I want to expand on Benjamin's point and make it practical: what an API call looks like in Power Query, how to think about endpoints and parameters, and how to practice safely with a friendly dataset like The Movie Database (TMDB).

APIs in plain English (and why this matters)

An API (Application Programming Interface) is a set of rules for asking a service for data or actions. For analytics work, most of what we do is simple:

You send a request (often an HTTP GET).
The service responds with data (often JSON).
You transform that response into a table.

Benjamin's key message is that this workflow is not exclusively for software engineers. Power Query is perfectly capable of doing the "ask" part, not only the "clean and shape" part.

Key takeaway from Benjamin Ejzenberg: if you can describe what you want, you can often retrieve it directly from the source with an API call.

What "calling an API" means in Power Query

Benjamin summarized it as:

You write a query
The API fetches the data for you
You get a table ready to use

Let me translate that into Power Query mechanics:

Build the URL to the API endpoint (the specific address that returns the data you want).
Send the request using Web.Contents().
Parse the response (usually Json.Document()).
Expand and transform the nested records and lists into a clean table.
Refresh on schedule in Power BI Service or re-run in Excel.

The big shift is this: instead of managing files, you manage a repeatable query.

A fun practice API: The Movie Database (TMDB)

Benjamin recommended TMDB for practice because it is free, well documented, and more fun than accounting tables. I agree. Training on movie data makes it easier to experiment with filters, sorting, pagination, and nested JSON.

What you typically need to do with TMDB:

Create an account
Generate an API key
Read the docs for an endpoint like "popular movies"

From there, Power Query can retrieve a live list of popular movies, including titles, release dates, ratings, and more.

Example: Fetch popular movies with a simple Power Query function

Benjamin mentioned an example that is "12 lines of code" to return a table. You can absolutely keep it lightweight. Below is a straightforward pattern you can adapt.

Step 1: Create a function that calls the endpoint

In Power Query (Power BI Desktop or Excel), you can start with a blank query and paste something like this (replace YOUR_KEY):

(apiKey as text, optional page as number) as table =>
let
    PageNumber = if page = null then 1 else page,
    Url = "https://api.themoviedb.org/3/movie/popular?api_key=" & apiKey & "&page=" & Number.ToText(PageNumber),
    Source = Json.Document(Web.Contents(Url)),
    Results = Source[results],
    AsTable = Table.FromList(Results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1", {"title", "release_date", "vote_average", "vote_count", "popularity"}, {"title", "release_date", "vote_average", "vote_count", "popularity"})
in
    Expanded

This returns a table of popular movies for a given page.

Step 2: Call the function

Create another query that calls your function, for example:

GetPopularMovies("YOUR_KEY", 1)

Now you have a refreshable table that came directly from an API, not from a downloaded CSV.

Making it robust: the parts people forget

Benjamin's post is intentionally encouraging and simple. To make API calls production-friendly, here are the common additions I usually make.

1) Handle pagination

Many APIs return results in pages. TMDB does. If you want more than the first page, you can generate a list of pages and combine them.

Conceptually:

Create a list: {1..5}
Transform each number into a table by calling the function
Combine all tables with Table.Combine()

This is where Power Query shines: list generation and table combination are core strengths.

2) Plan for rate limits

APIs often limit how many requests you can make per minute. If you loop through many pages or many IDs, you can hit rate limits.

Practical tips:

Pull only what you need (fewer columns, fewer pages).
Cache results when appropriate.
Avoid refreshing too frequently.

3) Expect schema changes and nulls

APIs evolve. A field might be missing for some records, or new fields appear.

In Power Query:

Expand fields defensively.
Use try ... otherwise when converting types.
Document which fields are required vs optional.

4) Manage credentials and privacy

A critical point: do not hardcode secrets in a way that gets shared.

Better options:

Store keys in parameters.
Use Power BI Service credential management.
Consider a gateway or key vault approach for more mature setups.

Also pay attention to Power Query privacy levels, especially when you combine API data with internal data sources.

Why this is a big deal for analysts

Benjamin's core promise is automation: "No need to download a file. No need to copy-paste from a website. The data arrives directly to you." That is not just convenience. It changes your workflow.

When you retrieve data via API:

Your dataset is reproducible.
Refresh becomes a feature, not a manual chore.
You can build dashboards that update themselves.
You can scale from one query to a library of reusable functions.

And importantly, you gain a transferable skill. The exact endpoint changes from one platform to another, but the pattern stays the same.

If you want to go further: what to try next

Benjamin asked a good question: what other APIs do people use for personal projects? If you are learning, pick an API that is:

Well documented
Has stable free access
Returns interesting, varied data

Ideas to explore beyond TMDB:

OpenWeather (weather data)
NASA APIs (astronomy photos, near-earth objects)
GitHub API (repos, issues, activity)
Public transport APIs (city dependent)

Each one will teach you a different lesson: authentication, pagination, nested JSON, or dealing with messy real-world responses.

Closing thought

Benjamin Ejzenberg's point that "APIs are not reserved for developers" is the mindset shift many data professionals need. Power Query is not just a cleaning tool, it is a connector and an automation engine. If you can build one simple API-powered table today, you can gradually level up to parameterized functions, multi-page extraction, and fully automated reporting.

This blog post expands on a viral LinkedIn post by Benjamin Ejzenberg, Transformez vos Excel chronophages en dashboards automatisés → Automatisation avec Power BI | Récupérez +20 modules gratuits ICI ⬇. View the original LinkedIn post →