Inspect column types and catch conversion errors
Verify a query's column types and find the rows that failed conversion.
~9 min read · intermediate · Module 3: Data types, locale and nulls
By Tahir Nas · Examples run in our simulator · Updated
Before this: Choose types deliberately: identifiers are text, null, empty text and errors are different things
The icon in each column header shows its type, but on a wide table it's easy to miss a column still marked ABC/123 (type any). Table.Schema lists every column with its type, so you can check the whole table in one view.
let
Source = #table({"Batch", "Weight", "Packed"}, {{"B-01", "12.5", "2026-10-01"}, {"B-02", "9", "2026-10-02"}}),
Typed = Table.TransformColumnTypes(Source, {{"Weight", type number}, {"Packed", type date}}),
Schema = Table.SelectColumns(Table.Schema(Typed), {"Name", "Kind"})
in
SchemaResult
| Name | Kind |
|---|---|
| Batch | any |
| Weight | number |
| Packed | date |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Why 'any' matters
- Excel loads an any column as whatever each cell happens to hold, so sorting and filtering can behave inconsistently.
- Power BI treats it as text in the model, so a numeric any column can't be summed in a visual.
- Custom columns created without the type argument are any too:
Table.AddColumn(T, "Margin", each …, type number)avoids that.
Count the rows a conversion broke
A type change doesn't fail the query when one value can't be converted. It stores an error in that cell and carries on. Table.SelectRowsWithErrors returns those rows, so you can count or inspect them before deciding what to do:
let
Source = #table({"Batch", "Weight"}, {{"B-01", "12.5"}, {"B-02", "n/a"}, {"B-03", "9"}, {"B-04", "unknown"}}),
Typed = Table.TransformColumnTypes(Source, {{"Batch", type text}, {"Weight", type number}}),
BadRows = Table.SelectRowsWithErrors(Typed, {"Weight"}),
BadCount = Table.RowCount(BadRows)
in
BadCountResult
2
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
A count like this makes a good refresh check: if it's not 0, someone should look at the source. The next module covers what to do with the error rows themselves.
A type review checklist
- Run Table.Schema on the final step and look for Any.Type.
- Check identifiers are text and measures are numbers.
- Check date columns are dates, not text that looks like dates.
- Count conversion errors per typed column.
Where to find this in Excel
- Excel for Windows
- View > Column quality (Data Preview group)
- What you see
- A bar under each header shows the share of valid, error and empty values. Profiling uses the first 1,000 rows unless you change it in the status bar.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Using the data profiling tools (Microsoft Learn); Data types in Power Query (Microsoft Learn).
Common mistakes
- Leaving custom columns untyped, so they load as any and behave like text in Power BI.
- Assuming a type change succeeded because the query didn't fail, without counting cell errors.
- Checking types only in the preview's first rows while the error is in row 5,000.
Excel, Power BI and the simulator
- Both hosts show the type icon in each column header and offer View → Column quality, which shows the percentage of valid, error and empty values. By default profiling uses the first 1,000 rows; switch to Column profiling based on entire data set in the status bar to check everything.
- Table.Schema works the same in Excel and Power BI Desktop and is handy in a separate check query.
- The simulator's Table.Schema returns Name, Position, TypeName and Kind only; rely on Kind when comparing with a real host.
Table.SchemaTable.SelectRowsWithErrorsTable.RowCountNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Data types and locale quiz