Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

Can Power BI Read JSON?

Cody Schneider••8 min read

Yes, Power BI can definitely read, process, and visualize data from JSON files. While it's not as straightforward as importing a simple CSV, Power BI’s built-in Power Query Editor is a powerful tool designed to unpack and transform nested JSON structures into clean, usable tables. This article will walk you through the entire process, from connecting to your JSON file to preparing the data for your reports and dashboards.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

What is JSON and Why Does It Matter?

Before we jump into the steps, let's quickly cover what JSON is. JSON stands for JavaScript Object Notation. It's a lightweight, easy-to-read text format used for sending data between servers and web applications. Think of it as a universal language for data on the web.

You’ll encounter JSON files frequently when working with modern software, especially when exporting data from SaaS platforms or connecting to web APIs. Its structure consists of key-value pairs, much like a dictionary. For example, a simple JSON object for a user might look like this:

{
  "userId": 12345,
  "name": "Jane Doe",
  "email": "jane.doe@example.com",
  "isActive": true
}

The challenge, and where Power BI's tools become essential, is that JSON can be nested. An object can contain other objects or an array (a list) of objects. This structure is efficient for applications but needs to be flattened into a traditional table format with rows and columns for analysis. That's exactly what we'll do.

How to Connect to JSON Data in Power BI

Getting your JSON file into Power BI is the easy part. The real work begins once the data is inside the Power Query Editor, where you’ll transform it into something you can actually use.

Step 1: Open Power BI and Select Your Data Source

First, open a blank Power BI Desktop report. On the Home tab, click on Get Data. This brings up a list of the most common data sources. Since JSON is a common file type, you might see it right there.

If not, click More... at the bottom of the list. In the Get Data window that appears, select File from the list on the left, then choose JSON from the options on the right and click Connect.

At this point, a standard file explorer window will open. Navigate to the location of your JSON file, select it, and click Open.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 2: Enter the Power Query Editor

Once you select your file, Power BI will automatically launch the Power Query Editor. This is your primary workbench for cleaning and transforming the data. What you see next depends entirely on the structure of your JSON file.

  • If your JSON file starts with a single object (enclosed in {}), you'll see a single line with the word Record that you can click to see its keys and values.
  • If your JSON file starts with an array of objects (enclosed in []), which is more common, you will see a list of Records.

Don’t worry if this initial view looks unhelpful. It's just the starting point. Our next job is to tell Power Query how to unpack these records into a readable table.

Unpacking Your JSON: From Nested Records to Usable Tables

This is where most people get stuck. Transforming nested JSON data is a multi-step process, but each step is logical. We'll use a sample JSON file representing online orders, which contains a nested array of line items.

Here’s what our sample data looks like:

[
  {
    "orderId": "SO-1001",
    "customer": {
      "customerId": "CUST-A",
      "name": "Acme Inc."
    },
    "orderDate": "2023-11-15",
    "items": [
      {
        "productId": "PROD-10",
        "productName": "Widget A",
        "quantity": 2,
        "price": 50.00
      },
      {
        "productId": "PROD-25",
        "productName": "Gadget B",
        "quantity": 1,
        "price": 125.00
      }
    ]
  },
  {
    "orderId": "SO-1002",
    "customer": {
      "customerId": "CUST-B",
      "name": "Beta Co."
    },
    "orderDate": "2023-11-16",
    "items": [
      {
        "productId": "PROD-10",
        "productName": "Widget A",
        "quantity": 5,
        "price": 50.00
      }
    ]
  }
]

Step 1: Convert the List to a Table

When our sample data loads, Power Query shows a list of two Records. Our first move is to turn this list into a proper table structure.

In the top left, under the Transform tab, click the Into Table button. A small dialog box will pop up, which you can just click OK on in most cases. Now, you have a table with a single column (probably named Column1) where each cell contains a Record.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Step 2: Expand the Top-Level Columns

Look at the header for Column1. Right next to the name, you'll see an icon with two arrows pointing in opposite directions. This is the Expand button. It tells Power Query to look inside each Record in the column and pull out the key-value pairs as new columns.

Click the Expand icon. A dropdown menu will appear, listing all the keys found in the first level of your JSON records: orderId, customer, orderDate, and items.

Make sure they are all checked. You'll also see an option that says "Use original column name as prefix." Uncheck this box to keep your column names clean (e.g., orderId instead of Column1.orderId). Click OK.

Your table now looks much better! You have columns for orderId and orderDate. However, the customer column still shows Record, and the items column shows List. We just need to repeat the process for these nested fields.

Step 3: Expand the Nested Objects (like customer)

Let's unpack the customer column next. Click the Expand icon on the customer column header. You'll see the nested keys: customerId and name.

Again, make sure both are checked, uncheck the "Use original column name as prefix" box, and click OK.

Your table transforms again, now with columns for customerId and name instead of the generic customer record.

Step 4: Expand the Nested Array to New Rows (like items)

This is the key step for handling one-to-many relationships, like an order that has multiple line items. The items column currently has the value List in each cell. This list contains the records for each product purchased in that order.

Click the Expand icon on the items column. This time, Power Query gives you a different option: Expand to New Rows. Select this.

This transformation is powerful. It duplicates the parent order information for each item in the nested items list. Our original two rows (one for each order) have now become three rows (one for each line item across both orders). Order SO-1001 now has two rows because it had two products.

After expanding to new rows, the items column will now contain Record cells instead of List cells. Click the Expand icon one last time. Select all the keys (productId, productName, quantity, price), uncheck the prefix option, and click OK.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 5: Clean Up and Set Data Types

Congratulations! You now have a perfectly flattened, tabular dataset that you can actually use for analysis. Your final table should have columns for everything: orderId, customerId, name, orderDate, productId, productName, quantity, and price.

Your last step in Power Query is to review the data types. Power Query is usually pretty smart about guessing, but it's good practice to check.

  • Click the icon next to each column header (like 'ABC' for text, '123' for whole number) and set the correct type.
  • orderDate should be set to Date or Date/Time.
  • quantity should be Whole Number.
  • price should be Decimal Number or Fixed decimal number (Currency).

Once everything looks good, click the Close & Apply button in the top-left corner of the Home tab. Power Query will close, and your clean data will be loaded into the Power BI model.

Visualizing Your JSON Data

Back in the main Power BI report view, your freshly prepared data is now available in the Data pane on the right-hand side. From this point forward, it behaves exactly like any other data source, whether from Excel, SQL, or any other connector.

You can start dragging fields onto the report canvas to create visuals:

  • Create a Table visual to see the raw, flattened data you just worked on.
  • Create a Bar Chart with productName on the axis and quantity as the value to see which products sell the most.
  • Add a new measure to calculate total revenue (SUMX('YourTable', 'YourTable'[quantity] * 'YourTable'[price])) and show it in a Card visual.

The possibilities are endless. You successfully took a complex, nested JSON file and turned it into actionable insights.

Final Thoughts

So, Power BI's ability to handle JSON is not just a 'yes' - it's incredibly robust. The key is understanding how to use the Power Query Editor to methodically expand records and lists until your data is in a flat, tabular format that's ready for analysis.

This entire process of connecting to various platforms, downloading data, and transforming it in tools like Power BI can become incredibly repetitive and time-consuming, especially for busy marketing and sales teams. We built Graphed to remove this friction. Instead of manual data exports and complex Power Query steps, we let you connect your data sources in a few clicks and build real-time dashboards just by describing what you need in plain English. For anyone who's spent hours wrangling data just to answer a simple question, Graphed turns that into a 30-second task.

Related Articles