Power BI JSON integration for files and web APIs
Power BI JSON integration turns nested JSON, the format most web services speak, into flat tables a report can use. Microsoft documents a JSON connector for files, and the Web connector reads JSON from a URL. So the connection itself is rarely the hard part. Instead, the work lies in flattening records and lists without creating duplicate rows.
- JSON and Web connectors
- Nested data flattened
- Stable refresh design
Last updated
How does Power BI JSON integration read a file or an API?
For a file, the author picks the JSON connector and browses to it. Power Query then opens with the content. Microsoft's page describes automatic table detection, which flattens the JSON into a table without manual steps.
For an API, the route is the Web connector. The author enters the address, signs in if the service asks, and Power Query reads the response as JSON. After that, the same flattening tools apply.
Many files at once need a different connector. Microsoft points to the Folder or SharePoint folder connector for that case. Then Power Query applies one set of steps to every file and appends the results.
Why does nested JSON cause wrong totals in Power BI?
JSON stores lists inside records. For example, one order holds a list of lines, and each line may hold a list of discounts. A table has no such nesting, so every list must expand into rows.
That is where totals go wrong. When two lists expand in the same query, each line repeats for each discount. As a result, order amounts count twice or more. The report looks fine until somebody checks it against the source.
So we split the document into several tables instead. Orders form one table, lines another and discounts a third, each with a key back to its parent. Then the model joins them, and every amount counts once. Shaping like this is a core part of Power Query consulting.
Where JSON data for Power BI usually comes from
The origin of the JSON decides the connector and the refresh route.
A web API
A cloud product returns JSON over HTTPS. So the Web connector calls it, often with a key and with paging.
Files in SharePoint
An export job drops files in a library. Then the SharePoint folder connector combines them on each refresh.
Files on a server
A local or network path works in Desktop. However, refresh in the service then needs an on-premises data gateway.
Common faults in a Power BI JSON integration
The same six problems appear in most JSON-based reports.
- Two lists expand in one query and double rows
- A new field appears and a step breaks
- An API returns one page, not all pages
- Dates arrive as text in mixed formats
- Keys hide inside query text for anyone to read
- Missing fields turn into errors, not blanks
Our steps for Power BI JSON integration
Four stages take a raw document to a trusted report, with a demo each week.
Study the structure
First we read sample documents and mark each record, list and key.
Quote a fixed scope
Then you receive a written quote that names the tables, measures and pages.
Flatten and model
We build one query per entity, set data types and relate the tables in your tenant.
Test odd cases
Finally, we feed in empty lists and missing fields, because real data always contains some.
Questions about JSON data in Power BI
Does Power BI have a native JSON connector?
Yes. Microsoft's Power Query connector list includes JSON, and it is generally available. It loads a local file and launches Power Query Editor. For JSON on the web, Microsoft directs authors to the Web connector instead. Both import data, so the model holds a copy that a refresh updates.
Can Power BI read JSON from a REST API?
Yes, through the Web connector. It sends a request and parses the JSON response. However, many services split results into pages and expect a key in a header. So the query needs a paging loop and a safe place for the secret. We build both as reusable functions.
Does a JSON file source need a gateway?
It depends on where the file sits. A file on a local drive or a company server needs an on-premises data gateway for refresh in the service. A file in SharePoint or OneDrive usually does not, because the service reaches it online. That is why we often move files to SharePoint first.
What happens when the JSON structure changes?
A query written for one shape can fail on another. For example, a renamed field breaks an expand step. So we write steps that tolerate missing fields and ignore new ones until somebody maps them. We also note which fields the report depends on, so developers know what not to rename.
How can Power BI Agency help with a JSON source?
Send a sample file or the API documentation, and one of our consultants will review it in a free 30-minute assessment. We answer within 2 hours. Power BI Agency is a small team, a division of Sataware Technologies, and independent of Microsoft. Phone (480) 485-7373 when a call is easier.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.