Mock APIs for Power BI — practice Power Query against an API you control

Power Query's Web connector is how Power BI (and Excel, and Dataflows) talks to REST APIs — and it has sharp edges that you usually meet for the first time in production: a hand-built URL that refreshes fine in Desktop but can't be scheduled in the service, pagination totals hidden in response headers M can only partially see, an automatic retry that fires without telling you, credential prompts you can't intercept, and a 100-second timeout. The usual practice targets — a real API with rate limits, or a static JSON file — can't rehearse any of that.

This guide points Web.Contents at a hosted, stateful mock instead: real endpoints, pagination you can reshape to whatever your M pattern needs, and failure modes you control from a URL param. No signup, works in Power BI Desktop's free tier, Excel, and Dataflows alike. Every curl below was verified against production before publishing.

1. Get Data → Web in 60 seconds

The public demo project is a seeded e-commerce API. In Power BI Desktop: Get Data → Web, paste:

https://mockbird.mockbird.workers.dev/m/demo/products?limit=20

Pick Anonymous authentication and connect. The Web connector sees JSON and wraps it in Json.Document automatically; you land in the Power Query editor holding a list of 20 records. To Table → expand the record column → columns id, name, price, category, inStock, rating appear, and you're doing real transform work against a real HTTP source instead of a pasted sample.

2. Make it survive scheduled refresh in the service

The classic trap: build the URL with string concatenation ("…/products?_page=" & Text.From(p)), publish, and the service refuses to schedule the refresh — a dynamic data source. Microsoft's documented exception is exactly the RelativePath and Query options of Web.Contents: keep the base URL static, move everything dynamic into the options record.

let
  Source = Json.Document(
    Web.Contents(
      "https://mockbird.mockbird.workers.dev",
      [
        RelativePath = "m/demo/products",
        Query = [_page = "2", _limit = "5"]
      ]
    )
  ),
  ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  Expanded = Table.ExpandRecordColumn(
    ToTable, "Column1", {"id", "name", "price", "category"})
in
  Expanded

Same request, refreshable shape — and Query values are URL-escaped for you, which pays off in §3 when a whole JSON template goes into a query param. This is the pattern worth building muscle memory for, and a mock is the right place to do the reps: check Data source settings shows a single static https://mockbird.mockbird.workers.dev entry, not one credential per hand-built URL.

3. Pagination with the total where M can actually see it

Mockbird's list endpoints take _page/_limit and return the collection total in an x-total-count header:

curl -si 'https://mockbird.mockbird.workers.dev/m/demo/products?_page=2&_limit=5' | grep -i x-total-count
# x-total-count: 30   (body: records 6–10)

But there's a catch the header-based tutorials skip: per Microsoft's own Web.Contents reference, outside a custom data connector only a subset of response headers is exposed on the result's metadata. Don't gamble your page-count logic on a header you may not be able to read — put the total in the body. mock_envelope reshapes any list response server-side; the same template that fixes this also emits page and hasMore:

# template: {"data":"$data","page":"$page","total":"$total","hasMore":"$hasMore"}
curl -s 'https://mockbird.mockbird.workers.dev/m/demo/products?_page=1&_limit=10&mock_envelope=%7B%22data%22%3A%22%24data%22%2C%22page%22%3A%22%24page%22%2C%22total%22%3A%22%24total%22%2C%22hasMore%22%3A%22%24hasMore%22%7D'
# → {"data":[…10 records…], "page":1, "total":30, "hasMore":true}
# same URL with _page=3 → "hasMore": false

Now the standard fetch-all-pages pattern is honest M you can paste and run — note the template goes through Query un-encoded; Power Query does the escaping:

let
  PageSize = 10,
  Envelope = "{""data"":""$data"",""page"":""$page"",""total"":""$total"",""hasMore"":""$hasMore""}",
  GetPage = (p as number) =>
    Json.Document(
      Web.Contents(
        "https://mockbird.mockbird.workers.dev",
        [
          RelativePath = "m/demo/products",
          Query = [
            _page = Text.From(p),
            _limit = Text.From(PageSize),
            mock_envelope = Envelope
          ]
        ]
      )
    ),
  PageCount = Number.RoundUp(GetPage(1)[total] / PageSize),
  AllRows = List.Combine(List.Transform({1 .. PageCount}, each GetPage(_)[data])),
  ToTable = Table.FromList(AllRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  Expanded = Table.ExpandRecordColumn(
    ToTable, "Column1", {"id", "name", "price", "category"})
in
  Expanded

30 rows from three requests, refreshable shape throughout. Change PageSize, watch the request count change in the inspector (§7). The API also does cursor pagination with a real next_cursor token if that's the model your real backend uses.

4. The retry you've never seen fire

Buried in the Power Query docs: Web.Contents automatically retries requests that fail with 408, 429, 503, 504, or 509 — up to three attempts, exponential back-off, and if the response carries a Retry-After header (delta-seconds, 0.5–120s) the engine waits exactly that long. Most people learn this when a dashboard refresh mysteriously takes minutes. Watch it happen instead:

https://mockbird.mockbird.workers.dev/m/demo/products?mock_seq=503,503,200&mock_seq_key=pq-retry-drill

mock_seq makes the endpoint answer 503, 503, then 200 (per key, then it keeps returning the last status). Point a query at it and refresh: the query comes back green — you never see the two failures. The public demo request inspector shows all three hits. Now you know why that flaky API "works fine in Power BI" and pages your on-call at 3am from everything else.

The 429 flavor, with a genuine Retry-After to honor:

# allow 3 requests per 60s for your key, then 429 + Retry-After
'https://mockbird.mockbird.workers.dev/m/demo/products?mock_ratelimit=3&mock_ratelimit_key=yourname&limit=1'
# 4 curls → 200 200 200 429   (retry-after: 41 on the 429)

A refresh that fans out into many page requests (§3's pattern, dozens of report visuals) is exactly how real APIs start rationing you. Here the engine hits the 429, reads Retry-After, waits out the window, retries, succeeds — a refresh that's slow on purpose, in a place where you can see why.

5. Error handling you can actually write

By default a non-200 fails the whole query. ManualStatusHandling turns chosen status codes into data you can branch on — and mock_status produces those codes on demand:

let
  Response = Web.Contents(
    "https://mockbird.mockbird.workers.dev",
    [
      RelativePath = "m/demo/products/1",
      Query = [mock_status = "404"],
      ManualStatusHandling = {404, 500, 503}
    ]
  ),
  Status = Value.Metadata(Response)[Response.Status],
  Result = if Status = 404 then null else Json.Document(Response)
in
  Result

Swap the mock_status value through your whole error matrix — 404, 500, 503 — and check each branch does what you intended. Two caveats worth rehearsing: listing a code in ManualStatusHandling also switches off §4's automatic retry for it (you asked for manual), and 401/403 can't be intercepted outside a custom connector — they raise a credentials exception. Try mock_status=401 once just to see what your report consumers will see when a real API's token expires; it isn't your error branch, it's a Power BI credentials prompt.

6. Slow sources and the 100-second default

curl -s -o /dev/null -w '%{time_total}\n' \
  'https://mockbird.mockbird.workers.dev/m/demo/products/1?mock_delay=3000'
# 3.79s measured

Web.Contents(url, [Timeout = #duration(0, 0, 0, 30)])  // override the 100s default

Add ?mock_delay=3000 (up to 5,000 ms) to any endpoint and a refresh becomes observably slow — useful for watching how Desktop reports progress, how long a Dataflow step sits "evaluating", and whether your Timeout override actually applies where you think it does.

7. Push the filtering to the source

Query folding doesn't happen against a Web source — Table.SelectRows after Json.Document filters on your machine, after downloading everything. With an API that filters server-side, the Query record is your fold:

Query = [category = "electronics"]          // exact match on any field
Query = [price_gte = "500"]                 // _gte/_lte/_gt/_lt/_ne/_like
Query = [q = "laptop"]                      // free-text search across fields
Query = [sortBy = "price", order = "desc"]  // server-side sort

Both verified above: category=electronics returns just the matching records, price_gte=500 returns 12 of 30. Practicing the where-does-the-filter-run question against a mock builds exactly the instinct that keeps real refreshes from downloading a warehouse.

8. See exactly what Power Query sends

When a real API rejects your request, the argument starts: is it the header, the encoding, the caching? End it with a bin — the demo's catch-all route echoes whatever arrives:

curl -s -X POST https://mockbird.mockbird.workers.dev/m/demo/hooks/powerbi-refresh \
  -H 'content-type: application/json' -d '{"report":"sales","source":"powerquery"}'
# → {"ok":true,"caught":"POST /hooks/powerbi-refresh","body":{…},"receivedAt":"…"}

Every hit — method, path, headers, body — lands in the public demo request inspector. It also settles Power Query's other classic mystery: did it even make a request? The engine caches aggressively within an evaluation; if your "refresh" isn't showing up in the inspector, you've just learned something about when Web.Contents really fires. On your own project, one ANY /* custom route is a private bin with a per-project inspector.

9. Your own API instead of the shared demo

One curl (or one click), no signup:

curl -s -X POST https://mockbird.mockbird.workers.dev/api/projects \
  -H "content-type: application/json" \
  -d '{"preset":"ecommerce"}'
# → {"id":"abc123xyz9", "adminKey":"…", ...}

Presets: blog / ecommerce / saas — or define resources by hand, or import an OpenAPI spec, db.json, or a HAR recording of the real API. Particularly BI-shaped: upload a CSV and it becomes a typed REST API — a realistic paged, filterable practice target made from your own data in one request. Every simulation param above works on your project's URLs, all of it behind a static base URL that keeps the service refresh happy. Projects have a 10,000 requests/day cap — report development barely dents it.

Where Power BI's built-ins are still the right tool

Fair's fair: if the data lives in SQL Server, a lakehouse, or an Excel file, use the native connector — real query folding against a real database beats anything the Web connector can do, and none of this guide applies. Reach for a hosted mock when the source is a REST API: then the refreshability pattern, pagination loop, retry behavior, error branches, and timeout handling are the work — and they deserve a practice target that can misbehave on demand instead of a production API that misbehaves on its schedule.

Verification: all demo curls on this page (limit=20 list returning a bare 20-element array with fields id/name/price/category/inStock/rating, _page=2&_limit=5 returning records 6–10 with x-total-count: 30, the envelope template returning {"data","page","total","hasMore"} with hasMore:false on page 3, a fresh-key mock_seq=503,503,200 run returning 503 503 200 200, a mock_ratelimit=3 run returning 200 200 200 429 with retry-after: 41 in delta-seconds plus x-ratelimit-* headers, mock_status 404 and 401, a 3.79s measured mock_delay=3000, category and price_gte=500 server-side filters, a POST→GET-back→DELETE cycle, and the bin echo with inspector log) were run against production on 20 Sep 2026 before publishing. Power Query behavior — the Web connector's Anonymous auth and automatic Json.Document wrapping, the dynamic-data-source scheduled-refresh limitation with the documented RelativePath/Query exception, the partial visibility of response headers outside custom data connectors, Web.Contents' automatic retry on 408/429/503/504/509 (up to three attempts, exponential back-off, Retry-After honored in delta-seconds between 0.5 and 120 seconds), ManualStatusHandling disabling built-in handling and being unavailable for 401/403 outside custom connectors, and the 100-second default timeout with the Timeout option — is from Microsoft Learn's Power Query documentation as of September 2026. We don't run your Power BI tenant; if a step here doesn't work, that's a bug: tell us.