How to transpose json data for power bi, tableau, and looker import
- Step 1Identify the layout your connector wants — Most BI connectors expand a top-level array of record objects into a table (one row per object). If your export is columnar (an object of arrays), you will convert it to records; if it is records and the target wants columns, you convert the other way.
- Step 2Open the JSON Transposer — Go to json-transposer. Processing is fully client-side — nothing is uploaded.
- Step 3Drop your JSON file — Drag the export
.jsononto the dropzone. Single top-level structure only: an array of objects, or a columnar object of arrays. - Step 4Pick the matching mode — Choose Columnar → array (
pivot-to-records) to get records for Power BI / Tableau, or Array → columnar (pivot-to-columnar) to produce column arrays. Flip keys ↔ values is unrelated to BI import. - Step 5Transpose and inspect — Click Transpose. For Columnar → array, confirm every record has all expected fields; the tool fills any short-column slot with
null, so a too-short column shows up as trailing nulls. - Step 6Load into the BI tool — Download
<name>.transposed.jsonand point Power BI (Get Data → JSON), Tableau (JSON file connector), or Looker's data source at it — or paste the records via your existing pipeline.
Preferred JSON shape by BI tool
What each connector parses most cleanly, and which transpose mode gets you there. The JSON Transposer changes top-level orientation only — it does not flatten nested fields.
| BI tool | Connector entry point | Wants | Use this mode |
|---|---|---|---|
| Power BI | Get Data → JSON (Json.Document) | Array of record objects → expand to rows | Columnar → array if source is columnar |
| Tableau | JSON file connector | Array of record objects; pick fields in the schema dialog | Columnar → array if source is columnar |
| Looker | Modeled table / file source | Flat record array, one object per row | Columnar → array if source is columnar |
| Columnar-array targets | Custom / Arrow-based loaders | Object of {field: [values]} | Array → columnar |
Columnar → array: null-fill behavior
When columns have unequal length, the records mode uses the longest column and fills missing slots with null. This is what an under-length column looks like after reshaping.
| Column | Values | Length | After Columnar → array |
|---|---|---|---|
| id | [1, 2, 3] | 3 | records get id 1, 2, 3 |
| name | ["A", "B"] | 2 (short) | record 3 gets "name": null |
| active | [true, false, true] | 3 | records get true, false, true |
| result | 3 records emitted | max length = 3 | the short column is null-padded |
Cookbook
Before/after JSON for BI import prep. Examples show the dropped file and the reshaped output the connector parses, with the tool's 2-space indentation.
Columnar export → record array for Power BI
ExampleA warehouse export shaped as columns won't expand to rows in Power BI. Columnar → array turns it into the record array Json.Document expands cleanly.
Input (export.json):
{
"id": [1, 2, 3],
"plan": ["pro", "free", "pro"],
"mrr": [49, 0, 49]
}
Mode: Columnar → array
Output:
[
{ "id": 1, "plan": "pro", "mrr": 49 },
{ "id": 2, "plan": "free", "mrr": 0 },
{ "id": 3, "plan": "pro", "mrr": 49 }
]Short column becomes trailing nulls
ExampleIf one column is shorter, the records mode fills the gap with null so Tableau/Power BI sees a consistent column it can type as nullable.
Input:
{
"id": [1, 2, 3],
"email": ["a@x.com", "b@x.com"]
}
Mode: Columnar → array
Output:
[
{ "id": 1, "email": "a@x.com" },
{ "id": 2, "email": "b@x.com" },
{ "id": 3, "email": null }
]Records → columnar for an Arrow loader
ExampleWhen the target loader wants column arrays, Array → columnar reshapes records into the columnar object it consumes.
Input (rows.json):
[
{ "region": "NA", "rev": 1200 },
{ "region": "EU", "rev": 900 }
]
Mode: Array → columnar
Output:
{
"region": ["NA", "EU"],
"rev": [1200, 900]
}Nested fields stay nested (flatten first)
ExampleBI connectors usually want flat columns. This tool keeps nested objects intact, so flatten before reshaping if the import should not nest.
Input:
[
{ "id": 1, "geo": { "country": "US" } }
]
Mode: Array → columnar
Output (geo is still an object):
{
"id": [1],
"geo": [ { "country": "US" } ]
}
// For a flat 'geo.country' column, run json-flattener first.Inconsistent record keys before import
ExampleRecords with different key sets pivot into ragged columns. Reshape after normalizing keys so the BI table has a stable, fully-populated schema.
Input (second record lacks 'tier'):
[
{ "id": 1, "tier": "gold" },
{ "id": 2 }
]
Mode: Array → columnar
Output (tier shorter than id):
{
"id": [1, 2],
"tier": ["gold"]
}Errors and edge cases
Real errors and silent failures sourced from each platform's own documentation. Match the wording to the row, fix what the row says to fix.
Columnar → array given an array
Rejectedpivot-to-records throws pivot-to-records requires a columnar object input. It expects a top-level object of arrays. If your source is already a record array, your connector may not need any reshape at all.
Array → columnar given an object
Rejectedpivot-to-columnar throws pivot-to-columnar requires an array of objects. Use Columnar → array to go the other direction, or check that your export really is a record array.
Columns of unequal length
Null-filledColumnar → array uses the longest column and fills every shorter column's missing slots with null. The BI tool then sees a nullable column rather than a hard error — but verify the nulls are expected, not a sign of truncated data.
Nested objects in records
PreservedNested objects/arrays are kept whole, not flattened into dot-notation columns. Many connectors then create struct/record columns. Run json-flattener first if you want flat scalar columns in the BI table.
Need a CSV/Excel data source
Wrong toolThis tool only emits JSON. If your BI workflow is CSV-based, convert with json-to-csv instead, then point the connector at the CSV.
A column value is not an array
Coerced to nullIn Columnar → array, any field whose value is not an array contributes null for every record (it is treated as having no per-row values). Make sure each top-level field really is an array of column values.
Empty object `{}` in records mode
ExpectedAn empty columnar object yields an empty array [] — no columns means no records. No error is thrown.
Invalid JSON export
Parse errorMalformed JSON fails at JSON.parse and nothing reshapes. Repair with json-format-fixer or check with json-validator first.
Extract larger than the tier limit
BlockedAbove 2 MB on Free / 100 MB on Pro the file is blocked. For bigger warehouse extracts, split or pre-aggregate before reshaping in the browser.
Mixed record + columnar source
Not supportedEach mode expects one clean shape. A payload that mixes records and column arrays at the top level cannot be reshaped in a single pass — separate the structures first.
Frequently asked questions
Which mode gives me a record array for Power BI?
Columnar → array (pivot-to-records) — but only if your source is columnar. If your export is already an array of record objects, Power BI's JSON connector expands it to rows without any transpose.
Does this output a Power BI dataset or just JSON?
Just JSON, reshaped. You then load that JSON via Get Data → JSON. The tool prepares the shape; the connector does the import.
Will it flatten nested fields for Tableau?
No. Nested objects stay nested. If your BI table should have flat columns, run json-flattener on the array first, then transpose.
What happens when my columns have different lengths?
Columnar → array uses the longest column and pads shorter columns with null for the missing records, so every record carries every field as a nullable value.
Can I produce a CSV for a CSV-based BI source?
Not here — output is always JSON. Use json-to-csv to generate a CSV, then point the connector at it.
Are numeric types preserved for column typing?
Yes. Numbers, booleans, and null pass through unchanged, so the BI tool can infer numeric and boolean column types instead of treating everything as text.
Why did Columnar → array reject my file?
It requires a top-level object of arrays. If you passed an array, the tool throws pivot-to-records requires a columnar object input — switch to Array → columnar or skip the reshape.
Is there any indent/format control?
No — the UI is the three mode radios only, and output is pretty-printed with 2-space indentation. Use json-minifier to compress if needed.
How large an extract can I reshape?
Free supports up to 2 MB; Pro up to 100 MB. This is a Pro tool, so a Pro subscription is required.
Does my extract get uploaded?
No. The reshape runs entirely in your browser; the file never touches a server.
My records have inconsistent keys — is that a problem?
Yes. Array → columnar produces ragged columns when keys differ between records. Normalize to a uniform key set first so the BI table has a stable schema.
Can it merge multiple JSON files for import?
No — it reshapes one file's top-level structure. To combine files use json-object-merger, then transpose the combined result.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.