Fixing “DataFormat.Error: We couldn’t parse the input as a Boolean.”

When loading data from SQL Server or transactional APIs into Excel through Power Query, queries often crash with the message: DataFormat.Error: We couldn't parse the input as a Boolean. This error occurs when Power Query attempts to convert a column into the Logical data type, but encounters values that do not conform to its strict definition of true and false.

In SQL Server, true/false values are frequently stored using the BIT data type (represented as 1, 0, or NULL), single-character codes ('Y', 'N', 'T', 'F'), or text strings ('True', 'False'). While humans understand that 1 means true and 0 means false, Power Query’s type logical parser only accepts native boolean states or text literals matching "true" and "false". Any other input stops query execution.

Quick Fix: Parse Booleans Reliably

To fix this error, remove the automatic Changed Type step in Power Query that assigns type logical. Instead, use Transform > Replace Values to convert 1 to TRUE and 0 to FALSE, or add a custom column with the formula: if [Column] = 1 or [Column] = "Y" or [Column] = "True" then true else false.

---

The Root Cause of Boolean Parsing Breaks

Power Query enforces strict type checking through the M language engine. The error occurs due to three primary scenarios:

1. SQL BIT Column Translation Inconsistencies: SQL Server’s BIT type represents binary states using 1 and 0. Depending on whether the connection is made through OLE DB, ODBC, or a custom native query, some drivers pass BIT fields as raw numeric integers rather than native boolean flags. If Power Query applies a type logical transformation directly over numeric 1 and 0 values, it throws DataFormat.Error.
2. Character and String Flags: Legacy databases often track boolean states using flags like 'Y'/'N', 'Yes'/'No', or 'Active'/'Inactive'. Power Query cannot parse these strings into booleans without explicit replacement logic.
3. Whitespace and Empty Strings: A column may contain text strings like "True " (with a trailing space) or empty strings ("") instead of true SQL NULL values. The parser rejects these entries. In some cases, unexpected nulls in logic steps cause Expression.Error: We cannot convert the value “null” to type Logical..
4. Aggressive Automatic Typing: When connecting to a database, Power Query automatically adds a “Changed Type” step by examining the top 1,000 rows. If those rows contain clean booleans, but row 1,050 contains a numeric character, an unexpected string, or a legacy code, the refresh will fail during execution. This behavior is detailed in Troubleshooting “Inferred Data Type” mistakes by Power Query..

[SQL Source Value]
       │
       ├── Native BIT / True Boolean ──► Passes cleanly as Logical (true / false)
       │
       ├── Numeric 1 / 0 ──────────────┐
       ├── Character 'Y' / 'N' ────────┼──► Direct conversion to 'type logical' FAILS
       ├── String "True " (w/ space) ──┤    Throws DataFormat.Error
       └── Empty String "" ────────────┘

---

Diagnostic Steps

To pinpoint which row and value triggered the break:

Step 1: Isolate the Problematic Step

Open Power Query (Data > Queries & Connections > Edit Query). In the Applied Steps pane, click through each step from top to bottom. The error will surface on the exact step attempting the conversion—typically named Changed Type.

Step 2: Extract the Offending Values

To view the exact values breaking the parser without scrolling through millions of records:

1. Select the step immediately before the failing Changed Type step.
2. Select the boolean column header.
3. Go to the Transform tab and click Count Distinct Values or open the column filter dropdown to inspect all distinct values present in the source table.
4. Look for strings like "0", "1", "NULL", "N", "Y", or empty text strings.

---

Solutions

Solution 1: Explicit Conditional Logic in M-Code (Most Robust)

The safest way to convert messy inputs into booleans is using an explicit conditional statement. This approach handles numbers, strings, and nulls in a single pass.

1. In Power Query, delete the failing Changed Type step.
2. Go to Add Column > Custom Column.
3. Name the new column (e.g., IsActive_Clean).
4. Enter the following formula:

if [IsActive] = 1 or [IsActive] = "1" or [IsActive] = "Y" or [IsActive] = "True" then true
else if [IsActive] = 0 or [IsActive] = "0" or [IsActive] = "N" or [IsActive] = "False" then false
else null

1. Set the data type of this new column to True/False (type logical).
2. Remove the original dirty column and rename the new column back to the original header name.

In advanced M-code, apply this conversion directly in place using Table.TransformColumns:

let
    Source = Sql.Database("ProductionServer", "CustomerDB"),
    DataTable = Source{[Schema="dbo",Item="AccountStatus"]}[Data],
    // Cleanly transform values in-place without generating parse errors
    CleanBoolean = Table.TransformColumns(DataTable, {{"IsSubscribed", each
        if _ = null then null
        else if _ = 1 or _ = "1" or _ = "Y" or _ = "True" then true
        else if _ = 0 or _ = "0" or _ = "N" or _ = "False" then false
        else null,
        type logical}})
in
    CleanBoolean

Solution 2: Replace Values Before Type Assignment

If you prefer using the graphical interface without writing custom M-code:

1. Select the column while it is still formatted as Text or Whole Number.
2. Go to Transform > Replace Values.
   • Value To Find: 1
   • Replace With: TRUE
3. Click OK.
4. Repeat the process:
   • Value To Find: 0
   • Replace With: FALSE
5. If the column contains empty text strings, replace "" with null.
6. Finally, click the column data type icon and select True/False.

Solution 3: Cast to Native BIT in the Source SQL Query

If you have permission to write or modify the extraction query, handle the logic inside SQL Server. SQL Server allows you to cast expressions directly into native BIT values, which modern drivers map correctly into Power Query:

SELECT
    AccountID,
    -- Convert string codes to native BIT
    CAST(CASE
        WHEN StatusCode IN ('Y', 'YES', '1', 'TRUE') THEN 1
        WHEN StatusCode IN ('N', 'NO', '0', 'FALSE') THEN 0
        ELSE NULL
    END AS BIT) AS IsActive
FROM Sales.Accounts;

---

Verifying the Solution

1. Verify Visual Display: In the loaded Excel table, true boolean values automatically align to the center of the cell and display in all-caps (TRUE or FALSE). If the value aligns to the left, it is stored as a text string.
2. Formula Compatibility: Test the column in a standard Excel formula. Enter =IF(A2, "Active", "Inactive"). A true boolean processes the condition without needing quotes (avoiding =IF(A2="TRUE", ...)). For more on formula pitfalls with logical data types, review #VALUE! in Boolean Logic: TRUE/FALSE vs Text “TRUE”/”FALSE”.
3. Data Model Compatibility: If loading into Power Pivot, confirm that the column appears with a checkmark or boolean indicator in the model diagram. For details on how mismatched types impact analytical calculations, see Troubleshooting “Data Type Mismatch” between SQL Server and Power Pivot..