Convert CSV to JSON for Power BI and Tableau

Default array, inference on. Revenue is a number and closed a boolean, so the model types them without manual steps.

Input (deals.csv):
deal,revenue,closed
A,12000,true
B,3500,false

Output (array, inference on):
[
  { "deal": "A", "revenue": 12000, "closed": true },
  { "deal": "B", "revenue": 3500, "closed": false }
]
→ Get Data → JSON → select this file

Grouped mode keys records under region so the navigator can expand a per-region structure.

Input:
customer,region,revenue
Acme,EMEA,12000
Globex,EMEA,8000
Initech,APAC,5000

Output (Grouped by column: region):
{
  "EMEA": [ { "customer": "Acme", "region": "EMEA", "revenue": 12000 }, … ],
  "APAC": [ { "customer": "Initech", "region": "APAC", "revenue": 5000 } ]
}

Accounting codes with leading zeros must stay exact. Turn inference off so they are clean dimension keys.

Input:
account,amount
0042,1500

Output (inference OFF):
[ { "account": "0042", "amount": "1500" } ]
→ account loads as a text dimension '0042'

A literal null keeps a measure column numeric with a blank value; an empty string would force the column to text.

Input:
month,sales
Jan,1000
Feb,null

Output:
[ { "month": "Jan", "sales": 1000 }, { "month": "Feb", "sales": null } ]
→ sales stays a numeric measure with a blank for Feb

Dates load as ISO text; set the column to Date in Power BI / Tableau. The converter does not emit a date type.

Output element:
{ "order": "A", "placed": "2026-06-12" }
→ in Power Query: Transform → Data Type → Date

A GL or cost-centre code 0042 becomes 42 with inference on, breaking the dimension key. Turn off Infer numbers, booleans, null so codes stay strings. Inference is global, so if your dataset mixes codes (want text) and measures (want numeric), convert with inference off and set numeric types in Power Query, or pre-clean the code column.

2026-06-12 stays a JSON string, so the BI tool loads it as text. Set the column's data type to Date in Power Query / Tableau (BI tools parse ISO dates reliably). The converter cannot emit a native date type because JSON has none.

An amount written "1,234.56" (comma thousands separator, inside quotes so it is one cell) does not match the numeric pattern, so it stays a string and loads as text. Strip the separator first (e.g. with csv-find-replace, comma → empty in that column) so it becomes a real number.

Power BI's JSON navigator does its own type inference on top of the JSON values; occasionally it picks the wrong type for a mostly-numeric column with one outlier. The JSON faithfully reflects the cells — fix it with one Changed Type step in Power Query. Inference on minimizes how often this happens.

Power BI's JSON connector and Tableau's JSON source expect an array (or grouped object), not newline-delimited records. Use Array or Grouped mode for BI. NDJSON is for warehouse/streaming loads, not these connectors.

When you group by region, each record keeps its region field as well as being under the region key — handy for BI, since the field is available as a column after the navigator expands the group. Remove it first with csv-column-remover only if you specifically don't want the duplicate.

A blank numeric cell becomes "" (empty string) with default settings, which can coerce a measure column to text in BI. Use a literal null cell for true nulls, or turn on Skip empty cells so the field is omitted and the column stays numeric with a blank.

Free tier caps at 2 MB / 500 rows — fine for a small report, not a full extract. Pro raises it to 100 MB / 100,000 rows. For very large datasets, the warehouse path (NDJSON → BigQuery/Snowflake → DirectQuery) usually beats a single JSON file; see the warehouse NDJSON guide.