JSON to CSV for Power BI: Prepare Your Data Before Import

Power BI can import JSON — but for most flat or lightly-nested data, converting to CSV first is faster and more predictable. The JSON to CSV converter does this in your browser in seconds. Import the CSV into Power BI exactly as you would any spreadsheet export. No Power Query M code, no JSON connector configuration.

Why CSV Beats Raw JSON for Most Power BI Imports

Power BI's JSON connector works, but it comes with friction:

• JSON imported via the connector appears as a "Record" or "List" column — you then have to expand it in Power Query, which requires clicking through expansion steps for each nested level
• Nested JSON often requires Power Query M code to properly flatten — not ideal for non-technical users
• Column data types need to be manually inferred or set after import

CSV import is simpler: Power BI reads columns directly, auto-detects data types, and presents a clean table in Power Query Editor immediately. For flat or lightly-nested data (most API responses, database exports, and CSV-origin data that was converted to JSON), CSV is the cleaner path into Power BI.

Step by Step: JSON to CSV to Power BI

1. Convert your JSON — open the JSON to CSV converter, paste your JSON array, and download the CSV
2. Open Power BI Desktop
3. Get Data > Text/CSV — navigate to your downloaded CSV file and click Open
4. Preview the data — Power BI shows a preview with auto-detected column types
5. Click Load (for direct load) or Transform Data (to open Power Query for any pre-processing)
6. Build your report — the JSON data is now a clean table in your Power BI model

For nested JSON, the converter flattens nested objects using dot notation. In Power BI, these appear as columns with names like address.city — you can rename them in Power Query by right-clicking the column header.

Sell Custom Apparel — We Handle Printing & Free Shipping

When Power Query's Native JSON Import Is Better

Power Query's native JSON connector is the right choice when:

• Your data source is a live API — Power Query can connect to REST APIs directly and refresh the data on a schedule. Pre-converting to CSV would break this refresh.
• You have deeply nested arrays (one-to-many relationships) — Power Query's "Expand to New Rows" feature handles this correctly. A CSV flattening approach doesn't represent one-to-many relationships well.
• The JSON file updates regularly — if you're getting a new JSON export every day and want Power BI to pick it up automatically, use Power Query's file connector and let it refresh.

For one-time imports, ad-hoc analysis, or data that doesn't need to refresh, the CSV approach is faster and requires less Power Query knowledge.

Common Issues Avoided by Pre-Converting to CSV

• Blank rows after import — JSON with trailing commas or null entries can create blank rows in Power Query. Pre-converting to CSV and inspecting the file first lets you catch this before it gets into your Power BI model.
• Wrong data types — Power Query sometimes guesses column types incorrectly from JSON. In CSV import, you can see and override types in the preview step.
• Nested column expansion errors — if nested objects have inconsistent structures across rows, Power Query expansion can error. The browser converter handles sparse nested objects (rows with missing nested fields) cleanly.
• Column name conflicts — dot-notation column names from the converter are unique. Power Query's automatic column expansion can sometimes create duplicate names when two levels of nesting have the same key.

Pre-conversion gives you an inspection step: open the CSV in Excel first, verify the data looks correct, then import the clean CSV into Power BI.

Frequently Asked Questions

Can Power BI import JSON files directly?

Yes, through Get Data > JSON. But the import process often requires Power Query steps to expand nested records. For flat or lightly-nested data, importing as CSV is faster and requires less Power Query configuration.

How do I connect Power BI to a live JSON API?

Use Get Data > Web, enter your API URL, and Power BI's Power Query connects and fetches the JSON. You'll need to expand the response in Power Query. For APIs requiring authentication headers, use Power Query's advanced M code editor. This path keeps data fresh on refresh — the CSV method doesn't.

Can I schedule a Power BI refresh using a CSV file?

Yes, if the CSV is in a location Power BI Service can reach (SharePoint, OneDrive). Set up the data source connection in Power BI Service and configure a refresh schedule. The CSV file must be updated externally — Power BI reads whatever is at that path on each refresh.

What delimiter should I use when converting JSON to CSV for Power BI?

Comma is standard and works by default in Power BI's CSV import. Use tab if your JSON values contain commas (addresses, descriptions) to avoid parsing errors. Power BI's Text/CSV import step lets you set the delimiter manually.