Handling “GUID” (Uniqueidentifier) conversion errors in Power Query.

In SQL Server, a Globally Unique Identifier (GUID) is stored as a 16-byte binary value under the uniqueidentifier data type. When importing these identifiers into Excel using Power Query, users frequently encounter evaluation errors, failed table merges, or broken relationships in the Power Pivot Data Model.

These issues occur because neither Power Query nor Excel contains a native 128-bit uniqueidentifier data type. Power Query must convert the incoming database binary representation into a 36-character hyphenated string (8-4-4-4-12 format). If this conversion is handled inconsistently across tables, or if different database drivers return conflicting representations, relationships fail and queries break.

Quick Fix: Normalize GUIDs to Consistent Text

In your source SQL query, convert the GUID to a standard string using CAST(MyGuidColumn AS VARCHAR(36)) AS MyGuid. Inside Power Query, ensure the column is typed as Text (type text) and apply uppercase normalization: Table.TransformColumns(Source, {{"MyGuid", Text.Upper, type text}}). This guarantees that string comparisons and relationship joins match accurately across all tables.

---

Why SQL GUIDs Cause Power Query Conversion Errors

The friction between SQL Server and Power Query over GUIDs stems from fundamental differences in how each system handles unique identifiers:

1. Missing Native Data Type: SQL Server understands that the binary sequence 0x4A2B... represents the formatted string e4b52b21-4f81-4b18-86d9-7e3f89025112. Power Query, however, only supports basic types: text, number, logical, date, datetime, datetimezone, duration, and binary. When importing a uniqueidentifier, Power Query must map it into either text or binary.
2. Case Sensitivity in Power Query Merges: In SQL Server, table joins on uniqueidentifier columns are case-insensitive. In Power Query’s M engine, string comparisons are strictly case-sensitive. If Table A contains E4B52B21-... (uppercase) and Table B contains e4b52b21-... (lowercase), a Power Query merge or join will fail to match any rows, returning blank results.
3. Driver Representation Mismatches: Depending on the driver used:
   • The Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) typically translates a uniqueidentifier into a hyphenated string automatically.
   • Generic ODBC or legacy SQL Server drivers may pass the GUID as a raw byte array (Binary.Type). If Power Query tries to assign a type text transformation directly over a binary payload, it throws Expression.Error: We cannot convert a value of type Binary to type Text.
4. Brackets and Hyphens Formatting: Some database systems or APIs export GUIDs wrapped in curly braces ({e4b52b21-4f81-4b18-86d9-7e3f89025112}), while SQL Server typically outputs bare strings. Mixing these formats breaks relational lookups.

For background on related data type conflicts in the analytical engine, review Troubleshooting “Data Type Mismatch” between SQL Server and Power Pivot..

---

Diagnostic Workflow: Identifying the GUID Failure Mode

Follow this diagnostic flow to pinpoint why your GUID column is failing:

[SQL uniqueidentifier Column]
             │
             ▼
[Inspect Power Query Header Icon]
             │
             ├── Icon shows '010' (Binary) ──► Driver passed raw bytes
             │                                 Fix: Convert Binary to Hex String
             │
             ├── Icon shows 'ABC' (Text) ────► Driver passed string
             │                                 Check for Case / Bracket mismatches
             │
             └── Displays Red Error Cells ───► M-Code type assertion mismatch
                                               Fix: Remove automatic Changed Type

Step 1: Check the Column’s Underlying Type

Open Power Query and look at the icon next to the GUID column header:

• “ABC” Icon: The column is recognized as text. If merges are returning zero matches, the issue is almost certainly caused by case mismatches (uppercase vs. lowercase) or surrounding curly braces.
• “010 / 101” Binary Icon: The connection driver failed to convert the 16-byte binary structure into a string. The column contains raw byte buffers instead of text.
• Red Error Bar: An automated step attempted an invalid direct conversion (such as converting raw binary directly to a number or unmapped object).

Step 2: Audit Joins and Merges

If a merge between two tables using GUIDs returns fewer rows than expected:

1. Select one GUID value from Table A and copy it.
2. Search for that value in Table B.
3. If the value exists visually but the merge fails, compare the casing (e.g., A-F characters) and verify whether one table contains enclosing { } brackets.

If missing GUID values are breaking relationships in the data model, see Handling “Null” values that break Power Pivot “Relationship” integrity..

---

Solutions for Clean GUID Import and Mapping

Solution 1: Convert to String in the SQL Query (Recommended)

Converting the uniqueidentifier into a standard string inside SQL Server eliminates driver translation issues and guarantees uniform formatting:

SELECT
    -- Cast to standard 36-character hyphenated string
    LOWER(CAST(CustomerID AS VARCHAR(36))) AS CustomerID,
    CustomerName,
    AccountBalance
FROM Sales.Customers;

Using LOWER() or UPPER() directly in the database query ensures that every GUID arrives in Power Query with identical casing, preventing case-sensitive merge failures.

Solution 2: Converting Raw Binary GUIDs in Power Query M-Code

If your driver imports the GUID as raw binary data (Binary.Type), you cannot simply change the data type dropdown to “Text.” Doing so triggers a type error. You must convert the binary buffer into a hexadecimal string.

In Power Query, add a custom column using this formula:

Binary.ToText([GuidBinaryColumn], BinaryEncoding.Hex)

To format the resulting 32-character hex string into standard 8-4-4-4-12 GUID notation, use this custom M transformation:

let
    Source = Sql.Database("DBServer", "AppDB"),
    RawData = Source{[Schema="dbo",Item="Transactions"]}[Data],

    // Convert binary to continuous hex string
    AddHex = Table.AddColumn(RawData, "HexGuid", each Binary.ToText([TransactionGUID], BinaryEncoding.Hex)),

    // Format into standard hyphenated GUID structure
    FormatGuid = Table.TransformColumns(AddHex, {{"HexGuid", each
        Text.Lower(
            Text.Range(_, 0, 8) & "-" &
            Text.Range(_, 8, 4) & "-" &
            Text.Range(_, 12, 4) & "-" &
            Text.Range(_, 16, 4) & "-" &
            Text.Range(_, 20, 12)
        ), type text}})
in
    FormatGuid

Solution 3: Standardize Text Formatting Across Tables

If your GUIDs import as text but fail during merge operations due to formatting discrepancies, apply a normalization step across all participating queries:

1. Select the GUID column in the Power Query editor.
2. In the Transform tab, click Format > Clean (removes non-printing bytes).
3. Click Format > Trim (removes accidental leading/trailing spaces).
4. Click Format > Uppercase (ensures case uniformity across all tables).
5. If some systems include curly braces, use Transform > Replace Values to replace { and } with nothing.

let
    Source = Sql.Database("DBServer", "AppDB"),
    TableData = Source{[Schema="dbo",Item="Orders"]}[Data],

    // Normalize string representation for reliable joins
    CleanGuid = Table.TransformColumns(TableData, {{"OrderGUID", each Text.Upper(Text.Trim(Text.Replace(Text.Replace(_, "{", ""), "}", ""))), type text}})
in
    CleanGuid

Solution 4: Handling Empty or Zeroed GUIDs

Databases frequently use the “empty GUID” (00000000-0000-0000-0000-000000000000) as a default placeholder for unassigned relationships.

If loaded directly into a Power Pivot dimension table, multiple empty GUIDs violate primary key uniqueness constraints and prevent relationship creation.

To fix this, replace empty GUIDs with null in foreign key tables, or assign them to a designated “Unassigned” record:

Table.ReplaceValue(SourceTable, "00000000-0000-0000-0000-000000000000", null, Replacer.ReplaceValue, {"CustomerGUID"})

If duplicate empty GUIDs trigger unique constraint errors on primary key columns, review Troubleshooting “Primary Key” violation errors during the Load-to-Data-Model phase.. If function calls fail when handling custom conversions, check Expression.Error: The parameter is incorrect (Function call mismatches)..

---

Verifying Relationship Integrity in Power Pivot

After normalizing your GUID columns:

1. Test the Merge: Perform a test merge in Power Query (Merge Queries). The preview bar at the bottom should indicate that the join matched the expected number of rows.
2. Examine Power Pivot Diagram View: Open the Power Pivot window (Power Pivot > Manage) and switch to Diagram View. Drag the sanitized GUID from the Fact table to the Dimension table. The relationship should establish as a clean One-to-Many (1:*) link without throwing primary key uniqueness errors.
3. Verify PivotTable Slicers: Build a quick PivotTable using attributes from the GUID-linked dimension table. Filter items with a Slicer and verify that metric values update correctly without returning empty blank categories.

For comprehensive architectural strategies when bridging database structures into Excel, see SQL-to-Excel Data Mapping: Fixing Type Mismatches and Schema Breaks..