Dynamic Web Queries with Parameters

By now, you’ve met the parakeets (parameters), dipped your toes into APIs, and discovered the invisible performance of query folding.

Today, we’re pulling it all together with by using parameters to dynamically generate web queries.

This isn't just about plugging a city name into a URL. This is how you:

• Page through an entire dataset one request at a time

• Change datasets dynamically based on user inputs

• Automate hundreds of API calls without writing a line of VBA (shudder)

  
  

Let’s get to work!

The Challenge: APIs Don’t Always Hand You Everything

Most APIs limit how much data you can fetch in one go, say, 100 rows per request. To get the full dataset, you need to make multiple calls, one for each page. Or maybe you're pulling from a system with filtered endpoints, and you want to change inputs dynamically.

That’s where Power Query + parameters come in.

Paginating Through a Public API

Let’s take a (simplified) API that returns 100 rows per page:

https://example.com/api/data?page=1

You want to request pages 1 through N and stitch them into one table. Here's how:

1. Create a list of page numbers

PageList = List.Numbers(1, 10)

2. Turn it into a table of URLs

Table.FromList(PageList, each "https://example.com/api/data?page=" & Text.From(_))

3. Use Web.Contents to call each URL

Table.AddColumn(Source, "Results", each Json.Document(Web.Contents([Column1])))

4. Expand and combine all rows from the nested results

Most Power Query users don’t realize

• You can build looping API calls using lists and parameters

• This avoids the need for staging files, macros, or external scripts

• It turns Power Query into a lightweight orchestrator

• You can parameterise almost any API structure: dates, categories, regions, languages

Tips and Pitfalls

• Throttle politely: Some APIs don’t like bulk calls. Insert a Function.InvokeAfter delay if needed.

• Cache carefully: Power Query doesn’t cache HTTP calls between refreshes.

• Skip validation when publishing to Power BI, or the Service might try to “validate” all pages.

• Use diagnostics: Query Diagnostics can tell you if Power Query is making too many separate web calls.

Experiment Starter: Activity API

Try this:

let

    IDs = List.Numbers(1, 5),

    URLs = List.Transform(IDs, each "https://www.boredapi.com/api/activity?key=" & Text.From(_)),

    Results = List.Transform(URLs, each Json.Document(Web.Contents(_))),

    Table = Table.FromList(Results, Record.ToTable)

in

    Table

And there you have it, 5 dynamic API calls, wrapped in one refresh. And if you need the number of calls to be dynamic, give us a message. We can help tailor your power query to your individual use case. (and we do visualisations as well!)