Power QueryExercises

Mixed column types after an append

Detect and repair columns whose values have different types after an append.

~8 min read · intermediate · Module 11: Appending tables

By Tahir Nas · Examples run in our simulator · Updated

Before this: Append tables and align their columns, Inspect column types and catch conversion errors

Two monthly extracts can have the same column names and still disagree about types. January's Qty came from a typed table; February's came from a CSV and is still text. Table.Combine doesn't convert anything, so the combined column holds a mixture.

let
    Jan = Table.TransformColumnTypes(#table({"OrderID", "Qty"}, {{"J-1", "2"}, {"J-2", "5"}}), {{"Qty", Int64.Type}}),
    Feb = #table({"OrderID", "Qty"}, {{"F-1", "3"}}),
    Combined = Table.Combine({Jan, Feb}),
    Check = Table.AddColumn(Combined, "Qty is number", each Value.Is([Qty], type number), type logical)
in
    Check

Result

OrderIDQtyQty is number
J-12true
J-25true
F-13false

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

In the preview the values look alike, but February's 3 is text. A sum fails on it, a filter like [Qty] > 2 raises an error for that row, and sorting puts it in an unexpected place.

The combined column type

When the inputs declare different types for a column, the combined column is typed as any. Table.Schema shows it:

let
    Jan = Table.TransformColumnTypes(#table({"OrderID", "Qty"}, {{"J-1", "2"}}), {{"Qty", Int64.Type}}),
    Feb = Table.TransformColumnTypes(#table({"OrderID", "Qty"}, {{"F-1", "3"}}), {{"Qty", type text}}),
    Combined = Table.Combine({Jan, Feb})
in
    Table.SelectColumns(Table.Schema(Combined), {"Name", "Kind"})

Result

NameKind
OrderIDany
Qtyany

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

OrderID is any too: neither input declared a type for it.

Fix types once, after combining

Set the types on the combined table. Table.TransformColumnTypes converts every value, whichever input it came from:

let
    Jan = Table.TransformColumnTypes(#table({"OrderID", "Qty"}, {{"J-1", "2"}, {"J-2", "5"}}), {{"Qty", Int64.Type}}),
    Feb = #table({"OrderID", "Qty"}, {{"F-1", "3"}}),
    Combined = Table.Combine({Jan, Feb}),
    Typed = Table.TransformColumnTypes(Combined, {{"OrderID", type text}, {"Qty", Int64.Type}}),
    Total = List.Sum(Typed[Qty])
in
    Total

Result

10

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Typing after the append also means one place to maintain types, instead of one per input. If an input contains a value that can't convert, the error shows up in the combined table where you can count it.

The same applies to dates: one source may deliver real dates while another delivers text like "2026-10-03". After the append, a single Table.TransformColumnTypes step converts the text values and leaves the real dates as they are.

Where to find this in Excel

Excel for Windows
Home > Append Queries, then Home > Data Type on the combined column
What you see
Set the type once after the append so values from every table convert the same way.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Append queries (Microsoft Learn); Data types in Power Query (Microsoft Learn).

Common mistakes

  • Assuming matching column names mean matching types.
  • Setting types in each input query and missing one, leaving a mixed column.
  • Converting a combined column with one culture when the inputs came from different locales.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop show the type icon ABC/123 for an any column after an append; that's the signal to add a Changed Type step.
  • In Power BI, an any column loads as text, so a mixed Qty column can't be summed in visuals until it's typed.
  • The simulator keeps each value's own type in an untyped column, as shown by Value.Is.
Functions in this lesson:Table.CombineValue.IsTable.TransformColumnTypes

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Appending tables quiz