Fixing “DataFormat.Error: The JSON input is invalid.”

The error DataFormat.Error: The JSON input is invalid occurs in Power Query when the Json.Document function attempts to parse text or binary data that does not follow standard JSON syntax rules. This failure typically happens when an API endpoint returns an HTML error page (such as a 403 Forbidden or 500 Server Error) instead of JSON, when the payload has been cut off by network or character limits, or when strings contain unescaped quotation marks and control characters.

Quick Answer

To resolve this error, check whether the source returned an HTML error page rather than JSON. Disable the Json.Document step temporarily to inspect the raw text. If the payload is valid but contains nulls or empty strings, wrap the step in a try ... otherwise expression or validate that the text begins with { or [.

---

Why JSON Parsing Fails in Power Query

The M function Json.Document parses JSON text or binary into native Power Query records and lists. JSON requires strict syntax: keys must be enclosed in double quotes, strings must escape special characters, and brackets must be balanced.

When Json.Document fails, it points to one of five underlying issues:

1. HTML Returned Instead of JSON: When a web API encounters an expired token, rate limit, or server crash, it rarely returns clean JSON. Instead, it returns an HTML error page beginning with <!DOCTYPE html> or <html>. Feeding HTML into Json.Document triggers an immediate syntax error.
2. Payload Truncation: If the text stream was cut off due to buffer limits or database export boundaries, the closing brackets (} or ]) will be missing, leaving an open-ended JSON structure.
3. Unescaped Quotes: Fields containing raw quotation marks (such as "Description": "Steel 12" Pipe") invalidate the document parser.
4. Empty Strings or Nulls: Passing an empty string ("") or a null into Json.Document causes a failure because empty inputs do not form a valid JSON document.
5. Byte Order Marks (BOM) and Encoding Issues: Text files saved with non-standard UTF encodings can insert hidden byte markers at the start of the string, corrupting the opening brace.

---

Step-by-Step Diagnostic and Resolution Path

1. Inspect the Raw Input

To see what Json.Document is actually trying to read:

1. In the Applied Steps pane, click the gear icon next to the parsing step, or click the step immediately before Json.Document.
2. Locate the column containing the raw payload.
3. Click inside the white space of an error row to inspect the text in the preview pane below.

If you see text starting with <html, <!DOCTYPE, or Unauthorized, your query is receiving an HTTP error page rather than data.

2. Handle HTTP Errors and Unauthorized Responses

If your API call returned HTML, the issue is authentication, permissions, or headers:

• Check if your API key or bearer token has expired.
• Check if the API requires explicit headers. In your Web.Contents call, ensure you specify Headers=[#"Accept"="application/json"].
• If the endpoint returned a 403 Forbidden error, see Solving “The remote server returned an error: (403) Forbidden.” to resolve header and permission barriers.

3. Filter Out Empty Strings and Nulls

If the column contains missing data, running Json.Document across every row causes the entire query to fail. Filter out blanks or handle them with conditional logic:

= Table.AddColumn(#"Previous Step", "ParsedJSON", each
    if [RawPayload] = null or [RawPayload] = "" then null
    else try Json.Document([RawPayload]) otherwise null
)

For safe handling of null records across your data transformation steps, review How to handle “null” values that break mathematical transformations.

4. Safely Handle Corrupted Rows with try ... otherwise

If you are processing millions of logs and a handful of rows contain malformed syntax, do not allow those isolated rows to abort the entire import:

= Table.TransformColumns(#"Previous Step", {{"JSONColumn", each try Json.Document(_) otherwise null}})

Rows with invalid syntax will quietly output null, allowing your valid data to load. To learn how to log and audit the specific rows that failed parsing, see Using try…otherwise in M-code to create custom error handling steps.

5. Check for Data Truncation

If the JSON string ends abruptly without closing brackets, verify where the data originated. If the text was pulled through an Excel cell or SQL export, it may have hit a character buffer. For resolving character limits on large text fields, refer to Troubleshooting “Truncated Data” errors in long text columns (>32k characters).