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

let
    ApiKey  = "qsk_live_...",
    BaseUrl = "api.querystreams.com",

    Response = Json.Document(
        Web.Contents(BaseUrl, [ ... ])
    )
in
    Response

See everything Query Streams can do beyond this guide Get Started Free

Supports

+more

Live Data

Replace exports with on-demand SQL

Excel Sheets

BI & AI Analytics

Ask Nova, get SQL + charts

Meet Nova

+more

Automation

Scheduled sync to 6+ platforms

Explore

+more

API to SQL

Query APIs with SQL, no code

Explore

Query Streams is a secure, real-time database integration platform that publishes any saved SQL query as an authenticated REST endpoint, then hands you the Power Query M to read it from Power BI. Learn more at QueryStreams.com and sign up for free to build your first endpoint and paste it into a report today.

Why Power BI usually needs a driver, a gateway, or both

The normal way to get a SQL database into Power BI is a native connector or an ODBC driver. That works well when the database sits somewhere Power BI can reach. It gets expensive the moment it does not.

If the database lives on your own network, Power BI cannot reach it directly, so Microsoft’s answer is the on-premises data gateway: a service you install, register, patch, and monitor, holding a credential set for every data source behind it. If the database is an engine Power BI has no first-party connector for, you are installing and version-matching an ODBC driver on every machine that opens the report. And in both cases the report itself holds a connection to the database, which means whoever can edit the dataset is one dialog away from the credentials.

A REST endpoint sidesteps all three problems, because Power BI already knows how to read JSON over HTTPS with no driver at all. The hard part was never Power BI’s side. It was producing a safe, parameterised, authenticated endpoint in front of a database without building an API service yourself. That is the part Query Streams does.

What the Power Query M export actually contains

Open any endpoint in the Query Streams portal, click ‘Export’, and choose ‘Power Query M (.pq)’ under Data Tools. You get a single let ... in ... expression, written for your endpoint, with your parameters already in it. Here is what a generated file looks like for a GET endpoint with two filters:

// ─────────────────────────────────────────────────────────────
//  Query Streams endpoint: Monthly Revenue by Region
//  Aggregated order totals grouped by sales region.
//
//  HOW TO USE
//    Excel:    Data → Get Data → From Other Sources → Blank Query
//              → Advanced Editor → paste this whole block.
//    Power BI: Home → Transform data → New Source → Blank Query
//              → Advanced Editor → paste this whole block.
//
//  Replace the placeholder API key on line 'ApiKey ='. Tweak
//  any parameter value in the 'Params' record. Run.
// ─────────────────────────────────────────────────────────────

let
    ApiKey  = "qsk_live_replace-me",

    // ── PARAMETERS ──────────────────────────────────────────
    //  Edit values below. Each comment shows the contract
    //  (required vs optional, type, default, description).
    //  'compression=none' is intentional: Power Query M has
    //  no LZ4 decoder, so we ask the API for raw JSON.
    Params  = [
        compression = "none",
        // required, date -- first day of the reporting window
        start_date = "2026-01-01",
        // optional, text, default "all" -- sales region filter
        region = "all"
    ],

    BaseUrl = "https://api.querystreams.com/v1/endpoints/8f2c1a94-...",

    Response = Json.Document(
        Web.Contents(
            BaseUrl,
            [
                Headers = [
                    #"X-API-Key" = ApiKey,
                    #"Accept"    = "application/json"
                ],
                Query   = Params
            ]
        )
    )
in
    Response

// ─────────────────────────────────────────────────────────────
//  NEXT STEPS (in the Power Query Editor)
//
//    1. Click the response preview to inspect the record.
//    2. For tabular results, click into the 'data' field.
//    3. Click 'To Table' then expand the record column to
//       break out the individual row fields.
// ─────────────────────────────────────────────────────────────

Four things in that file are doing more work than they look like they are.

Connect Power BI to your REST API in four steps

Why the export switches compression off

Query Streams endpoints compress their responses with LZ4 by default, which is why large result sets move quickly to the Excel add-in, the Google Sheets add-on, and application clients. Power Query M has no LZ4 decoder. Handed a compressed body it would read the framed bytes as garbage and fail somewhere unhelpful.

So the generated M always sends compression=none and the API returns plain JSON. It is the one line in the file you should not delete. It is also the reason a very wide result set will feel slower in Power BI than the same query does in the Excel add-in, which does decompress.

The same file works in Microsoft Excel

Power Query is the same engine in both products, so the exported .pq is not Power BI specific. In Microsoft Excel the path is ‘Data’, then ‘Get Data’, then ‘From Other Sources’, then ‘Blank Query’, then ‘Advanced Editor’ — paste the identical block. The generated file’s header comment lists both routes for exactly this reason.

That said, if Excel is where the data is going, the Query Streams Excel add-in is the better tool. It handles authentication for you, lists your saved queries in a sidebar, exposes filters as real controls, runs several queries into several worksheets at once, and uses the compressed transport. The Power Query M route exists for the cases the add-in does not cover: Power BI itself, locked-down Office deployments where add-ins are blocked, and datasets that need to refresh on a server-side schedule.

Power Query M against the usual Power BI connection routes

That last row is a genuine trade, not a marketing hedge. A native connector supports query folding, so a slicer in Power BI can rewrite the SQL that hits the database. A REST endpoint cannot do that: the shape of the query is fixed when you save it, and the report chooses from the parameters you exposed. Design the endpoint to return the grain the report needs, expose the filters that matter as parameters, and the difference stops mattering. Point a report at a raw fact table and expect Power Query to slice it and you will feel it.

Which databases Power BI can reach this way

Because Power BI only ever sees JSON, every engine Query Streams connects to looks identical from the report’s side. That includes Microsoft SQL Server, PostgreSQL, MySQL, MariaDB, Oracle, SQLite, Microsoft Access, Snowflake, BigQuery, and DuckDB, along with hosted variants like Amazon RDS, Azure SQL Database, Google Cloud SQL, Neon, Supabase, and PlanetScale. It also covers the API connectors — Stripe, HubSpot, Shopify, Google Analytics, Google Ads, Google Search Console — which means a Power BI report can read a SaaS platform as if it were a SQL table.

The endpoint is where the difference disappears. Whether the SQL underneath runs against Oracle on a machine in your server room or against a Postgres branch in the cloud, the M in your report is the same nine lines. See the full list on the database integrations page, or read how a saved query becomes an endpoint.

Frequently Asked Questions

Get Started

Related guides: Instant REST API for SQL Databases | Expose a PostgreSQL Database as a Secure REST API | Expose a SQL Server Database as a Secure REST API | All API Platform guides

Category: RestAPI Platform

Tags: power bi, rest api, power query, power query m, business intelligence, sql

Meta Description: Connect Power BI to any SQL database over a REST API. Generated Power Query M, no ODBC driver.