Power BI REST API: Connect Live SQL Data Without ODBC Drivers

Power BI · REST API instead of a driver

Point Power BI at a REST API, not a database driver

Power BI normally reaches a database through a driver on your machine and, for scheduled refresh, an on-premises gateway. A Query Streams endpoint is a plain HTTPS URL, so Power BI Desktop and the Power BI service read it with the built-in Web connector. The portal writes the Power Query M for you: Export → Power Query M (.pq), paste, add your key, done. The same file works in Excel.

About 5 minutes No driver, no gateway Data stays on your network Excel too

What you’ll end up with

A Power BI table fed by a saved SQL query over HTTPS, refreshable in the service without an on-premises gateway, because the endpoint looks like any web API. The endpoint itself looks like this in the portal console:

{
  "city": "Seattle"
}

curl -X POST "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19" \
  -H "X-API-Key: qsapi_YOUR_KEY" \
  -H "Content-Type: application/json" \
  -d '{"city": "Seattle"}'

{
  "columns": [ { "name": "customer_id", "type": "integer" }, { "name": "name", "type": "string" }, { "name": "city", "type": "string" } ],
  "rows": [
    { "customer_id": 1042, "name": "Northwind Traders", "city": "Seattle" },
    { "customer_id": 1057, "name": "Alpine Outfitters", "city": "Denver" },
    { "customer_id": 1063, "name": "Harbour Foods", "city": "Halifax" }
  ],
  "meta": { "row_count": 3, "agent_duration_ms": 41, "request_id": "req_7Hq2mKd9" }
}

The reason drivers exist is that Power BI has to speak each database’s wire protocol from wherever it runs, which is why scheduled refresh needs a gateway sitting inside your network. With an endpoint, the Agent that already sits with the database runs the query and api.querystreams.com answers Power BI over HTTPS, so there is nothing for Power BI to install and nothing for the service to tunnel into.

What you’ll need

A Query Streams account

Every plan has the REST API. Free gives you one endpoint, two keys and 250 rows per call, which is enough to build this guide. Sign up at my.querystreams.com.

The Agent on a computer that can reach your own database, with the connection added

Often the database server itself or an always-on computer on the same network.

A saved query in Query Builder

The endpoint wraps exactly one saved query. Write the SELECT, run it once, save it. Add a Dynamic Filter (a @variable in the WHERE clause) if callers should be able to filter.

Plus Power BI Desktop, or Excel 2016 or later. If you do not have an endpoint yet, the Database REST API hub takes you from a saved query to a URL in four steps.

Connect Power BI in four steps

1. Export the Power Query M file

   In API open your endpoint, click Export and choose Power Query M (.pq). The file is a complete let … in expression with the endpoint URL and your callable parameters already in place. It looks like this:

   Export · Power Query M (.pq)

   let
     Source = Json.Document(Web.Contents(
       "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19",
       [Headers = [#"X-API-Key" = "qsapi_YOUR_KEY"], Query = [compression = "none"]])),
     rows = Source[rows],
     table = Table.FromRecords(rows)
   in
     table

   The export always asks for compression=none, because Power Query cannot decode LZ4 frames; JSON over gzip is what Power BI expects.

2. Paste it into Power BI

   In Power BI Desktop choose Home → Transform data → New Source → Blank Query, open the Advanced Editor, replace its contents with the file and click Done. In Excel the path is Data → Get Data → From Other Sources → Blank Query, then the same Advanced Editor.

3. Add your key

   Replace the placeholder with your 49-character key starting qsapi_ from the endpoint’s Sharing tab. When Power BI asks how to connect, choose Anonymous: the key travels in the X-API-Key header the query already sets, so no Power BI credential is needed.

4. Load and refresh

   Click Close & Apply. The rows array becomes your table with one column per query column. Publish to the Power BI service and set a refresh schedule: because the source is a web URL, no on-premises gateway is required. Each refresh is one call on the endpoint’s Activity tab.

The request Power BI makes

Under the hood it is the same call you can make with curl. Parameters marked Callable can be passed from Power Query as Query = [city = "Seattle"]:

curl "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19?city=Seattle" \
  -H "X-API-Key: qsapi_YOUR_KEY"

{
  "columns": [ { "name": "customer_id", "type": "integer" }, { "name": "name", "type": "string" }, { "name": "city", "type": "string" } ],
  "rows": [
    { "customer_id": 1042, "name": "Northwind Traders", "city": "Seattle" },
    { "customer_id": 1057, "name": "Alpine Outfitters", "city": "Denver" },
    { "customer_id": 1063, "name": "Harbour Foods", "city": "Halifax" }
  ],
  "meta": { "row_count": 3, "agent_duration_ms": 41, "request_id": "req_7Hq2mKd9" }
}

Rows come back as objects keyed by column name, with the column list and a meta block that tells you how many rows there are, whether the result was clipped, and the request id you can quote to support. The same call as a POST puts the parameters in a JSON body, which is the right choice for long lists of values:

curl -X POST "https://api.querystreams.com/v1/endpoints/8f2c4d1e-6b3a-4f7e-9c21-5d0a7b3e2f19" \
  -H "X-API-Key: qsapi_YOUR_KEY" \
  -H "Content-Type: application/json" \
  -d '{"params": {"city": "Seattle"}}'

?format=csv

CSV instead of JSON, header row included. Accept: text/csv does the same. Streaming endpoints return NDJSON (one JSON object per line) with a final {"_summary": true, "row_count": N} line; check that last line.

?sample=true

At most five rows, not billed. Good for wiring up a client.

?compression=lz4

An LZ4-frame body for high-volume callers with a decoder; the Code tab has the decoder snippet per language. Plain gzip happens on its own when your client sends Accept-Encoding: gzip.

Authorization: Bearer qsapi_…

Accepted as an alias for X-API-Key. Use it from browser code.

Beyond Power BI

What the plans allow

Every call also has a fixed 60-second query timeout and an 8 MiB response cap, and the bytes it returns count against your plan’s monthly data allowance, the same allowance the Excel and Google Sheets add-ins use. Free has one endpoint and 5 MB a month, which comfortably covers a small table called many times a day. See the pricing page for the allowances by plan.

For a report, keep the query to the columns the visuals need and let the refresh schedule do the rest; Power BI reads the whole result on every refresh, so the static row cap of your plan is the ceiling for a single table.

Keeping it safe

When something doesn’t work

Every error body has the shape {"error": {"code": "…", "message": "…"}} and every response carries an X-QS-Request-Id header. Quote the id when you write to support.

Two Power Query specifics: a DataSource.Error naming 401 means the key in the M file is wrong or was revoked; Expression.Error: The field ‘rows’ of the record wasn’t found means the endpoint returned an error envelope, so run the URL in the portal console to read it.

Questions people ask

Related guides

• Database REST API: Turn Any SQL Query into an Endpoint
• Microsoft SQL Server REST API
• PostgreSQL REST API
• Connect REST APIs to Excel without VBA or Power Query