Overview

JSON (JavaScript Object Notation) is a lightweight, text-based data format commonly used for data exchange between systems, APIs, and web services. Unlike flat CSV files, JSON data is often nested and hierarchical, requiring transformation and normalization before it can be used effectively in Power BI's tabular data model.

Key Challenge: JSON stores data in a parent-child structure with nested objects and arrays, while Power BI works best with flat, normalized tables. The transformation process extracts this nested data into properly structured tables.

Understanding JSON Structure

What is JSON?

JSON is a text format that represents data using:

• Objects: Key-value pairs enclosed in curly braces {}
• Arrays: Ordered lists enclosed in square brackets []
• Values: Strings, numbers, booleans, null, or nested objects/arrays

Example JSON Structure

{
  "ProductID": 1,
  "ProductName": "Mountain Bike",
  "Category": {
    "CategoryID": 10,
    "CategoryName": "Bikes"
  },
  "Specifications": [
    {"Feature": "Frame", "Value": "Aluminum"},
    {"Feature": "Wheels", "Value": "29 inch"}
  ]
}

Analysis:

• ProductID and ProductName: Simple values (flat data)
• Category: Nested object (requires expansion)
• Specifications: Array of objects (requires expansion and normalization)

Step-by-Step: Importing JSON Files

Transforming and Normalizing JSON Data

Understanding the Initial State

When JSON loads, Power BI shows:

• Record: A single JSON object (key-value pairs)
• List: An array of items
• Table: Already structured data (rare)

Your Goal: Convert Records and Lists into flat table columns.

Common Transformation Steps

Step-by-Step Example: Complete Workflow

Sample JSON File: Customer Orders

orders.json:

Complete Transformation Process

Step 1: Import JSON

1. Home → Get Data → JSON
2. Select orders.json
3. Click Open
4. Power Query Editor opens

Initial View:

Column1
-------
List

Step 2: Convert List to Table

1. Click "To Table" in the ribbon (or right-click → To Table)
2. Click OK in the dialog
3. Column becomes Column1 with Record entries

Result:

Column1
-------
Record
Record

Step 3: Expand Top-Level Records

1. Click expand icon on Column1
2. Select all fields: OrderID, OrderDate, Customer, OrderLines
3. Uncheck "Use original column name as prefix"
4. Click OK

Result:

OrderID | OrderDate  | Customer | OrderLines
--------|------------|----------|------------
1001    | 2024-01-15 | Record   | List
1002    | 2024-01-16 | Record   | List

Step 4: Expand Customer Record

1. Click expand icon on Customer
2. Select: CustomerID, CustomerName
3. Click OK

Result:

OrderID | OrderDate  | CustomerID | CustomerName | OrderLines
--------|------------|------------|--------------|------------
1001    | 2024-01-15 | 501        | Acme Corp    | List
1002    | 2024-01-16 | 502        | TechStart    | List

Step 5: Duplicate Query for Normalization

1. Right-click query name in Queries pane
2. Select Duplicate
3. Rename queries:
• Original: Orders
• Duplicate: OrderLines

Step 6: Transform Orders Table

In the Orders query:

1. Delete OrderLines column (we don't need it in this table)
2. Change data types:
• OrderID: Whole Number
• OrderDate: Date
• CustomerID: Whole Number
3. Rename query to Orders (if not already)

Final Orders Table:

OrderID | OrderDate  | CustomerID | CustomerName
--------|------------|------------|-------------
1001    | 2024-01-15 | 501        | Acme Corp
1002    | 2024-01-16 | 502        | TechStart

Step 7: Transform OrderLines Table

In the OrderLines query:

1. Remove CustomerID, CustomerName columns (not needed here)
2. Expand OrderLines list:
• Click expand icon
• Select "Expand to New Rows"
3. Expand the resulting Record:
• Select: ProductID, ProductName, Quantity, UnitPrice
• Click OK
4. Change data types:
• OrderID: Whole Number
• ProductID: Whole Number
• Quantity: Whole Number
• UnitPrice: Decimal Number
5. Add calculated column for Line Total:
• Add Column tab → Custom Column
• Name: LineTotal
• Formula: [Quantity] * [UnitPrice]

Final OrderLines Table:

OrderID | ProductID | ProductName | Quantity | UnitPrice | LineTotal
--------|-----------|-------------|----------|-----------|----------
1001    | 10        | Widget      | 5        | 19.99     | 99.95
1001    | 20        | Gadget      | 3        | 29.99     | 89.97
1002    | 30        | Doohickey   | 10       | 9.99      | 99.90

Step 8: Close & Apply

1. Click Close & Apply (Home tab)
2. Power BI loads both tables into the data model

Step 9: Create Relationship

In Model view:

1. Drag OrderID from Orders table to OrderID in OrderLines table
2. Relationship created: One-to-Many (Orders → OrderLines)

Common Power Query M Functions for JSON

Table.FromRecords

Converts a list of records to a table:

= Table.FromRecords(Source)

Table.ExpandRecordColumn

Expands record-type columns:

= Table.ExpandRecordColumn(
    Source,
    "ColumnName",
    {"Field1", "Field2"},
    {"NewName1", "NewName2"}
)

Table.ExpandListColumn

Expands list-type columns to new rows:

= Table.ExpandListColumn(Source, "ListColumnName")

Json.Document

Parses JSON text into a structured format:

= Json.Document(File.Contents("C:\path\to\file.json"))

Best Practices

1. Always Transform Before Loading

• Never load raw JSON directly to model
• Always use Power Query Editor for transformation
• Verify data types and structure before loading

2. Normalize When Appropriate

Create separate tables when:

• One-to-many relationships exist
• Data will be filtered/analyzed independently
• You need to reduce data duplication

Keep denormalized when:

• Dataset is small (<1,000 rows)
• Analysis is simple
• Performance isn't a concern

3. Handle Nulls and Errors

• Replace null values with appropriate defaults
• Use try...otherwise for error handling
• Remove error rows or fix source data

4. Optimize Performance

• Remove unnecessary columns early in transformation
• Filter rows before expansion when possible
• Use Table.Buffer() for repeated transformations on same data

5. Document Your Transformations

• Rename queries with clear names
• Add descriptions to queries (Right-click → Properties)
• Use meaningful column names

Troubleshooting Common Issues

Issue 1: "We couldn't find any data formatted as a table"

Cause: JSON structure not recognized Solution:

• Click "To Table" button in ribbon
• Or right-click → To Table

Issue 2: All Columns Show "Record" or "List"

Cause: Data hasn't been expanded Solution: Click expand icon (⟷) on each column and expand fields

Issue 3: Too Many Rows After Expansion

Cause: Expanding lists creates one-to-many relationships Solution:

• This is expected behavior
• Consider creating separate tables
• Use relationships to connect tables

Issue 4: Data Types Are Wrong

Cause: Power BI guesses types from JSON Solution:

• Manually set correct types for each column
• Use Transform tab → Data Type dropdown

Issue 5: Column Names Are Too Long

Cause: Using "original column name as prefix" during expansion Solution:

• Uncheck this option when expanding
• Manually rename columns to shorter names

Exam Tips for PL-300

1. Know the difference between Record and List:
• Record = Single JSON object → Expand to columns
• List = Array → Expand to rows first, then columns
2. Understand normalization:
• When to create separate tables
• How to maintain relationships via common keys
3. Remember data type conversion:
• JSON imports often have wrong types
• Always verify and correct data types
4. Power Query steps matter:
• Order of transformations affects results
• Expand before filtering for best results
5. Recognize when to use duplicate queries:
• For creating dimension and fact tables from same JSON source

Summary Checklist

✅ Import JSON file or connect to JSON API ✅ Convert List to Table (if needed) ✅ Expand Record columns to extract fields ✅ Expand List columns to create new rows ✅ Handle nested levels (repeat expansion) ✅ Remove unnecessary columns ✅ Set correct data types ✅ Rename columns for clarity ✅ Consider normalization (duplicate query if needed) ✅ Create calculated columns if needed ✅ Close & Apply to load to model ✅ Create relationships between tables

This comprehensive guide covers everything you need to know about importing and transforming JSON files for the PL-300 exam and professional Power BI development!