How to convert excel to json for power bi data pipelines
- Step 1Prepare a single clean table — One header row, one measure/dimension per column, one fact per row. Name columns to match your Power BI dataset field names exactly to avoid extra mapping. Put the target sheet first — the converter reads only the first sheet.
- Step 2Drop the .xlsx or .xls file in — SheetJS parses it locally. The picker accepts
.xlsxand.xls. For a.csvextract, use csv-to-json first. - Step 3Keep 'Infer types' on for numeric measures — Leave Infer types on so measures are numbers and flags are booleans. Check the preview that no numeric column came through as a string — that usually means stray text in a cell.
- Step 4Choose Minified for REST or 2 spaces for inspection — For a Power BI push-dataset POST body, pick Minified to shrink the payload. For Azure Data Factory or Snowflake validation, use 2 spaces so you can eyeball field names and types.
- Step 5Download the JSON array — Click Download JSON. The output is a flat array of objects — the shape Power BI rows, ADF REST, and Snowflake
VARIANTloads expect. - Step 6POST to Power BI or stage in Azure / Snowflake — Call
POST /datasets/{datasetId}/tables/{tableName}/rowswith the array as the body, upload the file to an Azure Blob container for an ADF Copy Activity, or stage it forCOPY INTOin Snowflake. Validate the array first with json-validator.
Pipeline target vs converter output
The converter emits a flat JSON array of objects. How each target consumes it.
| Target | How it consumes the array | Indentation to use |
|---|---|---|
| Power BI push dataset | Array becomes the body of POST .../tables/{table}/rows; max 10,000 rows per call (Power BI limit) | Minified (smaller payload) |
| Azure Data Factory REST | Stage the file in Blob; a Copy Activity reads the JSON array via a JSON dataset | 2 spaces (easier debugging) |
| Snowflake COPY INTO | Load the array into a VARIANT column, then LATERAL FLATTEN into rows | Either — Snowflake ignores whitespace |
| Fabric / Dataflow Gen2 | Point a JSON connector at the staged array | 2 spaces |
Cell type → JSON, and pipeline behaviour (Infer types ON)
What each Excel cell becomes and how the downstream system treats it.
| Excel cell | JSON value | Downstream note |
|---|---|---|
125000 (measure) | 125000 | DAX SUM works; Snowflake NUMBER column |
| Real date cell | "2026-06-10T00:00:00.000Z" | Parsed as a date dimension by Power BI / Snowflake |
TRUE / FALSE | true / false | Boolean column / DAX TRUE() |
| Empty cell | null | Some REST schemas reject null measures — strip first |
Q3-2026 (text) | "Q3-2026" | String dimension |
Free vs Pro limits for this tool
Excel to JSON is a Pro tool. Free tier is for small extracts; production fact tables need Pro.
| Limit | Free | Pro |
|---|---|---|
| Max file size | 2 MB | 50 MB |
| Max rows | 500 | 100,000 |
| Files per run | 1 | 5 |
Cookbook
Real spreadsheet → analytics-pipeline conversions with the exact JSON output and the matching ingestion step. Sample figures are illustrative.
Monthly revenue sheet → Power BI push dataset
ExampleA revenue-by-region sheet becomes a typed array. Measures are numbers, so DAX aggregations work, and the minified array goes straight into the push-dataset POST body.
Sheet:
region | month | revenue | active
EMEA | 2026-05 | 125000 | TRUE
APAC | 2026-05 | 98000 | FALSE
Output (Minified):
[{"region":"EMEA","month":"2026-05","revenue":125000,"active":true},{"region":"APAC","month":"2026-05","revenue":98000,"active":false}]
POST /v1.0/myorg/datasets/{id}/tables/Revenue/rows (body = the array)Chunk a large array under the 10,000-row push limit
ExamplePower BI push datasets accept at most 10,000 rows per REST call. Convert the full sheet, then POST in 10,000-row slices.
Converter output: an array of 23,400 rows.
Client-side chunking before POST:
const rows = require('./revenue.json');
for (let i = 0; i < rows.length; i += 10000) {
await postRows(rows.slice(i, i + 10000)); // 3 calls
}Stage in Azure Blob for an ADF Copy Activity
ExampleKeep 2-space output so you can validate field names in the ADF preview, then upload the array file to Blob and point a JSON dataset at it.
Output (2 spaces) saved as revenue.json, uploaded to:
https://acct.blob.core.windows.net/staging/revenue.json
ADF: Source = JSON dataset (the blob) →
Mapping data flow → Sink = SQL / Synapse tableLoad into Snowflake via VARIANT + FLATTEN
ExampleStage the array, load it into a VARIANT column, then flatten it into typed rows. ISO date strings cast cleanly to DATE.
PUT file://revenue.json @my_stage;
COPY INTO raw_json (v) FROM @my_stage FILE_FORMAT=(TYPE=JSON);
INSERT INTO revenue
SELECT f.value:region::string, f.value:revenue::number,
f.value:month::date
FROM raw_json, LATERAL FLATTEN(input => v) f;Strip null measures a strict REST schema rejects
ExampleEmpty measure cells become null, which some push-dataset schemas reject. Strip nulls before POSTing.
Converter output:
[{"region":"LATAM","revenue":null,"active":true}]
After /tool/json-null-stripper:
[{"region":"LATAM","active":true}]
→ the revenue key is omitted, satisfying a no-null schemaErrors 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.
Power BI push dataset rejects more than 10,000 rows per call
API limitPower BI's PostRows endpoint accepts at most 10,000 rows per request. The converter has no chunking — split the array client-side into 10,000-row slices and POST sequentially (see the cookbook). For very large tables, prefer a Dataflow/JSON connector over push datasets.
Measure column came through as strings
Excel coercionIf a measure has any non-numeric cell (a stray N/A, a trailing space, a thousands separator Excel stored as text), inference returns a string for those cells and DAX/Snowflake numeric columns break. Clean the column to pure numbers in Excel, or fix individual values, then re-convert.
Date stored as a serial number
Excel coercionA date column formatted as General/Number holds the serial (e.g. 46184) rather than a date, so it converts to a number and your date dimension fails to parse. Re-format the column as Date in Excel before saving so the converter emits an ISO-8601 string.
Null measures rejected by the REST schema
Schema rejectEmpty cells become null. A push-dataset table defined with non-nullable numeric columns will reject rows carrying a null measure. Strip nulls with json-null-stripper before POSTing, or fill the blanks (e.g. 0) in Excel first.
Only the first sheet is converted
By designThe tool reads the first sheet only and has no sheet picker. For a workbook with one sheet per fact table, move each to the first position (or split into single-sheet files) and convert one at a time, staging each separately.
Output is a flat array, not a wrapped { value: [...] } envelope
By designSome REST APIs expect { "rows": [...] } or { "value": [...] }. The converter emits a bare array. Wrap it in your client code or your ADF pipeline expression before the call — the tool gives you the inner array.
Column names with spaces survive but may break DAX
PreservedHeader text is used verbatim, so Net Revenue stays "Net Revenue". Power BI allows spaces in field names, but some DAX expressions and connectors handle them awkwardly. If that bites, rename the headers to NetRevenue in Excel before converting (there is no rename option in this tool).
Mixed-type column produces inconsistent JSON
PreservedInference is per cell, so a column with 100, n/a, 250 yields a mix of numbers and strings. A typed pipeline column then fails to load some rows. Normalize the column in Excel, or turn Infer types off to send everything as strings and cast downstream.
Currency trailing zeros dropped
Expected125000.00 becomes the number 125000 — JSON numbers carry no trailing zeros. This is harmless for aggregation; only display formatting is affected, which Power BI / Snowflake apply at the visual/query layer.
Free tier size / row cap exceeded
Upgrade requiredFree tier limits this Pro tool to 2 MB and 500 rows; Pro raises it to 50 MB and 100,000 rows. A production fact table is blocked on free with an upgrade prompt. Trim for a test, or upgrade to Pro for the full extract.
Frequently asked questions
What is the Power BI push-dataset row limit per call?
Power BI's PostRows endpoint accepts up to 10,000 rows per request. The converter does not chunk — split the array into 10,000-row slices in your client and POST them in sequence, as shown in the cookbook. For datasets in the millions of rows, use a Dataflow with a JSON connector rather than push datasets.
Will my numeric measures arrive as numbers for DAX?
Yes, as long as the cell is genuinely numeric and Infer types is on. A measure column with a stray text cell (a N/A, a trailing space, or a text-stored thousands separator) yields a string for those cells, which breaks DAX. Clean the column to pure numbers in Excel and re-convert.
Do I need to transform dates in Power Query after import?
Usually not. Real Excel date cells serialize to ISO-8601 strings (2026-06-10T00:00:00.000Z), which Power BI and Snowflake parse into dates automatically. If a date came through as a number, the source column was formatted as General/Number — re-format it as a Date type in Excel and re-convert.
Can I keep column names that contain spaces?
Yes — header text is preserved verbatim (trimmed). Power BI allows spaces, but some DAX and connectors handle them inconsistently. The tool has no rename option, so if spaces cause trouble, rename the headers to PascalCase or camelCase in Excel before converting, or remap later with json-key-renamer.
My REST endpoint wants an object envelope, not a bare array. What do I do?
The converter always outputs a flat array. Wrap it in your client or ADF expression — e.g. { "rows": <array> } — before the call. The tool deliberately gives you just the inner array so you can shape the envelope to whichever API you target.
How do I keep the REST payload small?
Pick Minified indentation — it removes all whitespace, which meaningfully shrinks a large push-dataset POST body. Use json-minifier if you need to re-minify a file you previously saved pretty-printed.
Some measure cells are blank and my schema rejects null. How do I fix it?
Empty cells become null. Either fill the blanks (e.g. with 0) in Excel before converting, or strip the null keys from each row with json-null-stripper so the field is omitted rather than null.
Can I convert several fact-table sheets at once?
No — the tool reads only the first sheet. Move each sheet to the first tab position (or save each as its own file) and convert separately, staging each output for its pipeline. There is no sheet selector in the UI.
Is the workbook uploaded to JAD Apps?
No. Conversion is fully client-side via SheetJS. Revenue, budget projections, headcount, and any other confidential figures in the workbook are never transmitted to JAD Apps or any third party.
How do I load the array into Snowflake?
Stage the file, COPY INTO a VARIANT column with FILE_FORMAT=(TYPE=JSON), then LATERAL FLATTEN to expand the array into rows and cast each field (::number, ::date, etc.). ISO date strings cast cleanly with ::date. The cookbook shows the full SQL.
Should I validate the JSON before the pipeline run?
Yes. Paste the output into json-validator before POSTing or staging. A malformed array is the most common cause of an ADF Copy Activity or COPY INTO that fails partway through, and catching it before the run saves a debugging cycle.
What's the largest sheet I can convert?
On Pro, up to 50 MB and 100,000 rows. Free tier is capped at 2 MB and 500 rows (this is a Pro tool). For a production fact table beyond the Pro row cap, split it and POST/stage the chunks, or combine converted arrays with json-object-merger first.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.