How to Import JSON Data into Power BI

Working with JSON files in Power BI can look intimidating, especially when you open the file and see a wall of nested text. But once you understand the basic steps inside the Power Query Editor, you’ll be able to transform that complex data into clean, report-ready tables. This guide will walk you through several methods for importing and shaping JSON data into Power BI, from simple local files to pulling live data from a web API.

What Exactly Is JSON?

JSON, which stands for JavaScript Object Notation, is a lightweight and easy-to-read format for structuring and sending data. It's incredibly popular, used by web applications, APIs, and configuration files because it’s both human-readable and easy for machines to parse.

JSON data is organized in key-value pairs. Think of it like a dictionary where you have a word (the key) and its definition (the value). Keys are always strings in double quotes, and values can be strings, numbers, booleans (true/false), arrays (lists), or even other JSON objects (which is how we get nested data).

For example, a simple JSON object describing a user might look like this:

As you can see, the "roles" key has a list of values, and the "profile" key contains another nested object. This structure is what we need to unpack inside Power BI.

How to Import a Local JSON File

This is the most straightforward method and a great place to start. If you’ve downloaded a report or data export as a .json file, you can pull it directly into Power BI.

Step 1: Get Data

From the Power BI Desktop Home ribbon, click on the Get Data dropdown. If you don’t see JSON in the common list, select More… to open up the full list of available data connectors.

Step 2: Select the JSON Connector

In the Get Data window, you can either filter by "File" on the left and select JSON from the list or simply type "JSON" into the search bar. Once you've selected it, click Connect.

Step 3: Browse for Your File

A file browser window will appear. Navigate to the location of your .json file, select it, and click Open. For this tutorial, let's assume we have a simple JSON file containing information about a few products like the one below:

Step 4: Enter the Power Query Editor

Power BI won't immediately load this data into a report view. Instead, it will automatically open the Power Query Editor. This is where the magic happens. Power Query is designed for data transformation, and it's here that you'll turn that nested JSON structure into traditional rows and columns.

Transforming JSON Data in Power Query

JSON data rarely arrives in the perfect tabular format you need for building visuals. In Power Query, you'll see your imported data one of two ways: as a single Record or a List of Records.

Based on our example file, Power Query correctly identifies that the file contains a list of objects and will present you with a list of Records. Your job is to convert this list into a structured table.

Step 1: Convert the List to a Table

When your JSON data is imported as a list of Record items, you’ll see a list on your screen. The first step is to convert it into a table structure. Go to the Transform tab in the ribbon and click the To Table button. A small dialog box will pop up, but you can usually just click OK to accept the default settings.

You’ll now have a single-column table named "Column1," where each cell contains a clickable Record link.

Step 2: Expand the Records into Columns

This is the most important step in the process. Look at the header for "Column1." To the right of the name, you’ll see an expand icon, which looks like two arrows pointing in opposite directions. This button allows you to "un-pack" the key-value pairs inside each Record.

Click the expand icon. A dropdown menu will appear showing all the keys found within your JSON objects (e.g., productID, name, price, inventory, tags).

Make sure all the columns you need are checked.
Important: Uncheck the box that says "Use original column name as prefix." This prevents Power Query from naming your new columns "Column1.productID," "Column1.name," etc., keeping your table clean.

Click OK. Power Query will now transform your single column of records into a multi-column table, where each key from your JSON object becomes a separate column. You'll see columns for productID, name, and price. However, notice that the inventory and tags columns still contain Record and List links, respectively.

Step 3: Keep Expanding Nested Data

Any nested objects or arrays will appear as columns containing Record or List links. You just need to repeat the expansion process for them.

For Nested Objects (Records): Click the expand icon on the inventory column header. You'll see options to expand quantity and location. Uncheck the prefix option again and click OK. Now you have separate columns for quantity and location.
For Nested Arrays (Lists): Click the expand icon on the tags column header. When expanding a list, Power Query gives you a different option: Expand to New Rows. Selecting this will duplicate the parent row for each item in the list. In our case, the row for the Water Bottle would be repeated three times, once for each tag ("reusable," "steel," "BPA-free"). This is a great way to flatten and analyze data that has multiple categories or tags.

Continue this process until all Record and List links have been expanded and you are left with simple, singular values in your columns. Finally, set correct data types for each column (e.g., text, whole number, decimal number) and click Close & Apply from the Home tab to load your clean data into Power BI for dashboarding.

Connecting to JSON from a Web API

A common use case for marketers and salespeople is pulling data directly from a SaaS platform via a web API, which almost always returns data in JSON format. The process is very similar, but your starting point is different.

Instead of choosing the JSON connector, go to Get Data -> Web. In the dialog box, simply paste the URL of the API endpoint you want to connect to. In a real-world scenario, you might have to provide authentication details, such as an API key, under the "Advanced" options, but for public APIs, the URL is all you need.

Once you click OK, Power BI fetches the JSON data from the URL, and from that point forward, you’ll be placed right back in the Power Query Editor. The transformation process of converting lists to tables and expanding columns is exactly the same as with a local file.

Handling JSON Lines Files (.jsonl)

While less common, you might run into JSON Lines format, often with a .jsonl extension. This format contains a separate, self-contained JSON object on each line of the file. This is common for logs or streaming data because it can be processed one line at a time. The standard Power BI JSON connector will fail with this format because it expects a single, complete JSON object or array in the whole file.

The solution is to treat it as a text file and parse each line manually using small M Code commands in Power Query.

Start with Power BI's standard File connectors: navigate to Get Data -> Text/CSV and select the .jsonl file. Power Query will show a preview with a single column where each row contains the JSON string. Click Transform Data.
With Column1 selected, go to Add Column and select Custom Column.
In the new Custom Column window, name your column ("ParsedJSON", for example) and enter the following formula:

Click OK. This will add a new column that contains each row's JSON string converted into a clickable Record link.
You can now right-click to remove the original raw "Column1," and from that point forward, you'll be able to expand the "ParsedJSON" column as previously discussed, transforming it into a new table in your desired format once it's available.

Final Thoughts

Importing JSON into Power BI, whether from local files or web APIs, involves transforming its nested structure into a flat table using Power Query's intuitive expanding interface. By mastering the process of turning records and lists into tables, you unlock the ability to create detailed and robust reports from complex data sources.

Instead of manually downloading numerous files and wrangling APIs from various platforms, Power BI simplifies dashboard creation significantly. At Graphed, we focus on connecting you to your essential data sources for platforms like Google Analytics, Shopify, HubSpot, or Salesforce. This way, you can accomplish these tasks effortlessly without the need for complex tools like Power BI. Our dashboard builder is designed to be straightforward, providing real-time insights quickly. We encourage you to explore how our platform can enhance your data analysis and reporting processes.