DataFormat.Error: The input is not a valid Base64 string.

The error DataFormat.Error: The input is not a valid Base64 string occurs in Power Query when the Binary.FromText function attempts to decode text using BinaryEncoding.Base64, but encounters characters or formatting that violate Base64 specifications. This happens most often when handling API responses, web-scraped images, or security tokens that contain URL-safe substitutions, missing padding characters, metadata headers (such as data URIs), or illegal whitespace.

Quick Answer

To resolve this error, remove any data URI prefixes (such as data:image/png;base64,), replace URL-safe characters (- with +, and _ with /), and ensure the total character count is a multiple of 4 by appending = padding characters. Also verify that null values are replaced before decoding.

---

Why Base64 Decoding Breaks in Power Query

Base64 is a binary-to-text encoding scheme that represents binary data using 64 specific ASCII characters: A-Z, a-z, 0-9, +, and /. The = character is used as trailing padding.

When you call Binary.FromText([Column], BinaryEncoding.Base64), Power Query requires strict compliance. The engine throws DataFormat.Error if it runs into any of the following issues:

1. Embedded Data URI Schemes: Base64 strings exported from web services or HTML elements often begin with a prefix such as data:image/jpeg;base64, or data:application/pdf;base64,. These metadata prefixes contain non-Base64 characters like colons, semicolons, and commas.
2. URL-Safe Encoding (RFC 4648 §5): Web APIs frequently convert + to and / to _ to safely transmit data inside URLs without percent-encoding. Standard Base64 decoders do not recognize or _.
3. Missing Trailing Padding (=): A valid Base64 string must always have a total character length that is a multiple of 4. Many modern web APIs strip the trailing = characters to save bandwidth.
4. Illegal Whitespace and Line Breaks: Raw Base64 payloads copied from email headers or MIME streams often contain carriage returns (\r\n) or space characters.
5. Null or Empty Values: Passing a null or empty string ("") directly into Binary.FromText immediately raises an error. To handle null logic safely in your pipeline, review How to handle “null” values that break mathematical transformations.

---

Step-by-Step Diagnostic and Repair Pipeline

To fix the invalid string, clean and format the text inside Power Query before passing it to Binary.FromText.

Step 1: Strip Data URI Prefixes

If your string begins with a descriptor like data:...;base64,, remove everything up to and including the comma:

= Table.AddColumn(#"Previous Step", "CleanedPrefix", each
    if [Base64String] = null then null
    else if Text.Contains([Base64String], ",") then Text.AfterDelimiter([Base64String], ",")
    else [Base64String]
)

Step 2: Convert URL-Safe Characters to Standard Base64

Replace the URL-safe characters - and _ with the standard Base64 characters + and /:

= Table.TransformColumns(#"CleanedPrefix", {{"CleanedPrefix", each
    Text.Replace(Text.Replace(_, "-", "+"), "_", "/"), type text
}})

Step 3: Remove Whitespace and Non-Printable Characters

Clean the string to remove any hidden line breaks, tabs, or rogue spaces. If your source data originates from database queries that include non-printable characters, see Fixing “Invalid Cell Value” when SQL returns non-printable ASCII characters.

In Power Query, apply:

= Table.TransformColumns(#"ReplacedChars", {{"CleanedPrefix", each
    Text.Trim(Text.Clean(_)), type text
}})

Step 4: Fix Missing Padding (=)

A valid Base64 string must have a length divisible by 4. If characters were stripped, determine the remainder and append the required = characters:

• Remainder of 2: Add ==
• Remainder of 3: Add =
• Remainder of 1: The string is corrupted (an individual byte cannot be represented by a single Base64 character).

Use this custom M expression to automatically fix padding:

= Table.AddColumn(#"CleanedText", "ProperlyPadded", each
    let
        str = [CleanedPrefix],
        len = if str = null then 0 else Text.Length(str),
        mod = Number.Mod(len, 4),
        padded =
            if str = null or len = 0 then null
            else if mod = 2 then str & "=="
            else if mod = 3 then str & "="
            else if mod = 1 then null // Corrupted string; cannot pad
            else str
    in
        padded
)

Step 5: Safely Decode with try ... otherwise

Once the text is cleaned and padded, apply Binary.FromText. Wrap the function in a try ... otherwise block to prevent individual corrupt rows from halting your entire query refresh:

= Table.AddColumn(#"ProperlyPadded", "DecodedBinary", each
    try Binary.FromText([ProperlyPadded], BinaryEncoding.Base64)
    otherwise null
)

To learn more about implementing error-trapping patterns across complex M queries, read Using try…otherwise in M-code to create custom error handling steps.

---

What to Do If Decoding Still Fails

If Binary.FromText still throws an error after stripping prefixes, restoring characters, and adding padding:

• Inspect Character Encoding: The source text may be Base64-encoded UTF-16 instead of standard UTF-8/ASCII. If the binary output decodes into unreadable characters or throws an encoding mismatch, you will need to decode the binary result into text using Text.FromBinary(binaryData, TextEncoding.Utf16).
• Check for Truncated Strings: If your Base64 text was imported through a source that truncated the field at 4,000, 8,000, or 32,767 characters, the end of the payload is missing. Base64 cannot decode if the final bytes are absent.