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
CheckResult
| OrderID | Qty | Qty is number |
|---|---|---|
| J-1 | 2 | true |
| J-2 | 5 | true |
| F-1 | 3 | false |
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
| Name | Kind |
|---|---|
| OrderID | any |
| Qty | any |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
TotalResult
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.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Appending tables quiz