How to Load JSON File in Power BI

If you've ever worked with APIs or web data, chances are you've run into JSON files. This common data format is a bit different from a simple spreadsheet, which can make importing it into a tool like Power BI seem tricky at first. This article will show you exactly how to load JSON data into Power BI, covering everything from a single file to an entire folder of them, so you can start building your reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a JSON File, Anyway?

Before diving into Power BI, let's quickly clarify what a JSON file is. JSON stands for JavaScript Object Notation. It's a lightweight, text-based format for structuring and sending data that's easy for both humans and machines to read.

Think of it as a set of descriptive key-value pairs. For example, instead of just seeing "John Smith" in a spreadsheet cell, a JSON file might represent a person like this:

{
  "firstName": "John",
  "lastName": "Smith",
  "isStudent": true,
  "courses": [
    { "courseName": "Data 101", "credits": 3 },
    { "courseName": "BI Basics", "credits": 4 }
  ]
}

Because of its flexibility and readability, JSON is the go-to format for APIs (Application Programming Interfaces), which is how different software platforms talk to each other. When you pull data from a marketing platform, a web service, or a SaaS tool, it often arrives as a JSON file.

How to Load a Single JSON File in Power BI: Step-by-Step

Let's start with the most common scenario: importing a single JSON file. This process happens mostly within the Power Query Editor, Power BI's built-in tool for transforming data.

Step 1: Open Power BI and Get Data

In the Home tab of the Power BI Desktop ribbon, click on Get data. This will open a menu with the most common data sources.

If you don’t see JSON listed, click on More… at the bottom of the list. This opens the full Get Data window.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Select the JSON Connector

In the Get Data window, you can either filter by "File" on the left-hand menu or use the search bar to type "JSON." Select JSON from the list and click Connect.

Step 3: Choose Your File

An explorer window will open, allowing you to navigate to the location of your JSON file. Find your file, select it, and click Open. Power BI will then analyze the file and open the Power Query Editor.

Step 4: Understand the Power Query Preview

Here’s where things look different from importing an Excel file. Instead of a grid of data, Power BI often shows the JSON file as a "Record" or a "List."

• A Record represents a single JSON object with key-value pairs (like our "John Smith" example earlier). You'll see clickable links for values that are nested, like other records or a list.
• A List represents a JSON array, which is a collection of items (often a collection of records).

Don't be alarmed by this, it's just how Power Query initially interprets the file's structure.

Step 5: Convert the Data to a Table

To turn this data into a usable table format, look for the To Table button. It’s located in the Transform tab in the ribbon. Click it.

Power Query will present you with a small dialog box. For the most part, you can accept the defaults and just click OK. This will convert the list of records into a single column, where each cell in that column contains a full record.

Step 6: Expand the Records into Columns

Now you have a table, but it's just one column of records. Notice the column header has a button with two arrows pointing in opposite directions. This is the Expand button.

Click this icon. Power BI will show you a checklist of all the available keys (fields) within the records. You can select the specific fields you want to turn into columns. For starters, it's usually fine to select all of them.

Pro Tip: Uncheck the "Use original column name as prefix" box. If you leave it checked, your new columns will have names like "Column1.firstName" and "Column1.lastName." Unchecking it keeps the column names clean (e.g., "firstName").

After clicking OK, you'll see your JSON data neatly organized into a familiar table with rows and columns.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 7: Clean and Finalize Your Data

If your JSON has nested objects (like the courses field in our example), one of the new columns might still show "List" or "Record." No problem! Just click the Expand icon on that column again and repeat the process until all your data is visible.

Finally, perform any standard data cleaning you need:

• Check Data Types: Power BI is smart, but sometimes it guesses wrong. Make sure number columns are formatted as numbers and dates are formatted as dates by clicking the icon to the left of each column header.
• Rename Columns: Double-click any column header to give it a more user-friendly name.

Step 8: Close & Apply

Once you’re happy with the table, click Close & Apply in the top-left corner of the Power Query Editor. This loads your clean JSON data into the Power BI data model, ready for you to create visuals.

How to Load Multiple JSON Files from a Folder

What if you have dozens or even hundreds of JSON files - say, daily exports from an application? Loading them one by one isn't practical. Instead, you can have Power BI import and combine them all at once.

Step 1: Use the "Folder" Connector

Go to Get data > More… just like before. But this time, select the Folder connector and click Connect. This connector is designed to handle multiple files that share a similar structure.

Step 2: Point Power BI to Your Folder

Click Browse… and navigate to the folder containing all your JSON files. Make sure only the files you want to import are in this folder to avoid errors. Click OK.

Step 3: Combine & Transform the Files

Power BI will show you a preview of the files within that folder. You’ll see information like the file name, extension, and date created. At the bottom, you'll see a few options. Click Combine & Transform Data.

This tells Power BI that you want to stack these files on top of one another and perform transformations on them in Power Query.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Configure the 'Combine' Steps

A new dialog box called "Combine files" will appear. It's asking you to demonstrate how to process a single sample file. Power BI will then record your steps and apply that same logic to every single file in the folder.

Select a sample file from the dropdown (usually the first file is fine) and click OK. Power Query will now do two things:

1. It will create a set of "helper queries" on the left pane. These are the recorded steps of transforming the sample file. You can mostly ignore these.
2. It will open a main query showing your combined data, with a new column that usually contains the data content.

Step 5: Expand and Format Your Combined Data

You’ll see your now-familiar Expand icon on the column containing the JSON records. Just as you did with the single file, click this icon to expose the fields, uncheck the prefix option, and expand the data into columns and rows.

The beauty of this method is that even if you add new JSON files to the folder later, all you have to do is click "Refresh" in Power BI, and the new data will be automatically loaded and processed with the same rules.

Final Thoughts

You can now confidently turn complex JSON data into clean, structured tables ready for visualization in Power BI. By following these steps for either single or multiple files, you've unlocked the ability to report on pretty much any data you can get your hands on from modern web-based applications.

Mastering tools like a Power Query editor is powerful, but it still often involves hours of managing file sources, configuring transformations, and debugging errors to build the dashboards your team needs. When we faced these manual reporting headaches, we created Graphed to be a much faster way. We connect directly to your marketing and sales tools and use natural language to generate live, interactive dashboards in seconds, so you can stop wrestling with connectors and back-end setup and just get answers to your questions.