Talking to APIs without being a developer

Sofia Ng
1 day ago
3 min read

We have met the mighty parakeets, Power Query’s parameters, and learned how they help us cleanly separate logic from values. This week, we’re going further: APIs.

Now before you back away slowly, you don’t need to be a developer. You don’t even need to write code. If you can use Power Query, paste a URL, and click a few buttons you’re already most of the way there.

A mouse holding a platter with API methods

So, What’s an API Anyway?

Psst we have a post about it - here

An API is basically a translator that helps different pieces of software talk to each other. Say you want to pull today's weather data into your spreadsheet. You don't need to figure out how the weather service stores their data or learn their internal systems. The API takes your simple request - "give me Sydney's weather" - grabs the data from wherever it lives, and hands it back in a format you can work with.

Of course, you need to know which API has the data you're after. Your accounting software has its own API for financial data, your CRM has one for customer info, and weather services have theirs for meteorological data etc. Each API speaks a slightly different dialect and has its own rules about what you can ask for and how often.

Power Query can speak this language, you just need to give it the right URL.

A Simple Use Case: Pulling Data from a Public API

Let’s say we want to get a daily cat fact. Yes, really. There’s a public API for that:

https://catfact.ninja/fact

In Power Query:

Go to Data > Get Data > From Web
Paste the URL above
Power Query will return a JSON blob
Click "Into Table", then expand the record to extract the fact

Just like that, you’ve pulled real-time data from the web—without code.

Making It Dynamic

Now if we combine this Web request with our post on parameters we start getting a bit more flexible and powerful.

Many APIs let you pass parameters in the URL, like:

https://api.weatherapi.com/v1/current.json?key=XYZ&q=Melbourne

You can:

Create a parameter called City
Replace Melbourne in the query with City
Let users select a city, and Power Query will fetch the corresponding weather

It’s like plugging your parakeets, parameters, into the internet.

Why This Matters

APIs open the door to:

Weather, finance, transport, or event data
Internal systems with published endpoints
Automated report refreshes from online data
Building dashboards with live external feeds

This makes Power Query a lightweight, GUI-friendly ETL tool, even for people who don’t code.

But Wait, there are some gotchas

This is where beginner guides usually stop. Here’s what they miss:

Disable PreValidation When Publishing to Power BI Service

Power BI may fail to refresh your dataset if it can’t validate all web calls during publish.
Set SkipTestConnection = true in your Web.Contents options (in Power BI Desktop, not Excel).

Avoid Auto Pagination Loops in APIs

Many APIs return partial data (e.g., 100 records per page).
Solution: Use a parameterized paging loop (e.g., increment pageNumber) with a stop condition.
This can be automated in Power Query using List.Generate. (we might write a blog post on this, let us know if this would be helpful)

Be Careful With API Keys

Never store secrets in plain text if publishing to the Power BI service.

Web.Contents vs Web.BrowserContents

Use Web.Contents whenever possible, it’s faster, respects caching, and is fold-friendly.
Web.BrowserContents launches a simulated browser session, slower and often breaks refresh.
(we might write a blog post on this, let us know if this would be helpful)

Always Inspect the API Response Structure

Some APIs wrap useful data in deeply nested objects or arrays.
Use the Record > To Table > Expand pattern to extract fields cleanly.
Don’t just "click until it works" structure matters for reusability and automation.