Power QueryExercises

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
    Schema

Result

NameKind
Batchany
Weightnumber
Packeddate

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

Batch was never typed, so it's any. Set it to text before loading.

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
    BadCount

Result

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

  1. Run Table.Schema on the final step and look for Any.Type.
  2. Check identifiers are text and measures are numbers.
  3. Check date columns are dates, not text that looks like dates.
  4. 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.
Functions in this lesson:Table.SchemaTable.SelectRowsWithErrorsTable.RowCount

Next 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