Inspecting Column Structure with Power Query Table.Schema: Detecting Missing Required Columns Before Ingestion

About This Article
This article was created using an automated generation workflow leveraging generative AI. We reviewed the specifications of Microsoft Learn for Table.Schema, Table.ColumnNames, Table.HasColumns, and Table.FromRows, and organized Power Query M examples to extract column information using dummy tables.

Verification Status: 📘 Microsoft Official Specifications Verified – Power Query Physical Device Unverified
The tables and results below are expected values derived from official specifications. Execution verification in Excel or Power BI has not yet been performed.

When importing monthly CSV files or department-specific Excel sheets using Power Query, empty data is not the only problem. Issues such as a column disappearing from the previous month, changed column names, or amount columns recognized as strings can also cause processing failures due tochanges in the table structure.

Power Query M includes a function to inspect table structure called Table.Schema. It displays not only column names, but also column positions, data type information, and whether nulls are allowed. Using a three-row dummy table, we will observe the structure and introduce methods to halt processing when a column is missing or to determine the presence of columns using Boolean values.

What Table.Schema Returns

The basic syntax is as follows.

Table.Schema(table as table) as table

This function does not return each row of the source data, but rather returns a separate table describing each column of the source table row by row. For example, if the original table has three columns: Order ID, Department, and Amount, the schema side will generally contain information corresponding to those three columns.

While other details such as precision and maximum length may also be present, some fields may not be available depending on the source. An empty metadata field does not necessarily mean data corruption.

Official Specification:Microsoft Learn — Table.Schema

How to Try It in Excel

Create a blank query in the Excel Power Query Editor and paste the following M code into the Advanced Editor. No external file loading or database connection is required.

let
    Source = Table.FromRows(
        {
            {1001, "Sales", 1250.5},
            {1002, "Support", 900.0},
            {1003, "Sales", 0.0}
        },
        type table [
            OrderId = Int64.Type,
            Department = text,
            Amount = number
        ]
    ),
    Schema = Table.Schema(Source),
    ImportantFields = Table.SelectColumns(
        Schema,
        {"Name", "Position", "TypeName", "Kind", "IsNullable"}
    )
in
    ImportantFields

Here, Table.FromRows creates a table from a list of row data. The second argument supplies not only column names but also a table type of type table [...]. Therefore, the sample declares types such that OrderId is an integer, Department is text, and Amount is a number.

Table.Schema(Source), many available schema columns are returned. In this case, Table.SelectColumns is used to keep only 5 items for better readability.

How to interpret the displayed information

The expected original column sequence is as follows.

The actual representation of TypeName and Kind depends on the Power Query engine and the source type representation. The "Meaning of types" above explains how to read them and does not reproduce the actual output of the type name field verbatim.

The important point is that Position starts at 0. Do not start counting from 1 as you would when counting Excel column numbers.

Change one aspect and observe the results

First, let's modify the final Amount = number as follows.

type table [
    OrderId = Int64.Type,
    Department = text,
    Amount = text
]

If you execute this as is, even though the original sample contains numeric values, you will verify the impact of changing the table's type declaration to text in the schema. This is an experiment to understand the difference between 'type checking actual data values' and 'inspecting type information declared for columns'.

Note: You should not assume that simply changing the type declaration means all existing values have been safely converted as desired. You must separately verify whether the values are in the expected format and whether there are any conversion errors.

Stopping subsequent processing when a column disappears

Suppose that the required columns in the monthly CSV were always OrderId and Amount. If Amount is missing from the data received from the responsible department, you should not overlook it and continue aggregation.

The following example compares the required columns with the actually existing columns. Please execute this as a separate empty query from the previous query.

let
    Source = Table.FromRows(
        {
            {1001, "Sales"},
            {1002, "Support"}
        },
        type table [OrderId = Int64.Type, Department = text]
    ),
    Required = {"OrderId", "Amount"},
    Existing = Table.ColumnNames(Source),
    Missing = List.Difference(Required, Existing),
    Checked =
        if List.IsEmpty(Missing)
        then Source
        else error (
            "Required column(s) missing: "
            & Text.Combine(Missing, ", ")
        )
in
    Checked

Here, the actual columns are {"OrderId", "Department"} and the required columns are {"OrderId", "Amount"}.

List.Difference(Required, Existing) extracts only the columns that are required but do not exist. Since the result is {"Amount"}, the process is expected to result in the following error depending on the conditions.

Required column(s) missing: Amount

Phrases like "If there is no amount column, aggregate only the remaining columns"It can be used to preventsilent miscalculations.

However, this method does not absorb failures in the data source itself or authentication failures. If the connection fails before retrieving column information, that connection error must be investigated separately.

To retrieve only boolean values, use Table.HasColumns

If you do not need detailed column information and only want to determine whether the required columns exist, there is a shorter function.

Table.HasColumns(table as table, columns as any) as logical

This function returns true if all specified columns exist, and false if even one is missing.

let
    Source = #table(
        type table [OrderId = Int64.Type, Amount = number],
        {{1001, 12.5}, {1002, 18.0}}
    ),
    HasRequired = Table.HasColumns(Source, {"OrderId", "Amount"}),
    HasOptional = Table.HasColumns(Source, {"OrderId", "Owner"})
in
    #table(

        type table [Check = text, Exists = logical],
        {
            {"OrderId + Amount", HasRequired},
            {"OrderId + Owner", HasOptional}
        }
    )

The expected results are as follows.

Official Specification:Microsoft Learn — Table.HasColumns

How to Choose Between the Three Functions?

Table.Schema is not always optimal. If you only need to check for existence, Table.HasColumns is sufficient. On the other hand, if you are investigating specification changes in CSVs or databases, Table.Schema is effective.

When applying to actual CSV imports

In practice, performing checks in the following order makes it easier to isolate the cause.

1. Can it connect to the data source?

2. Do all required column names exist?

3. Are the data types set for each column as expected?

4. Do errors occur during necessary type conversions?

5. Are there any nulls or duplicates in the required key columns?

6. Are the aggregation results for counts and amounts valid?

Table.Schema isthe material for the third structural check, and it is not a function that automatically proves items 4 through 6.

For example, AmountEven if is declared as a numeric type, if an external CSV contains strings such as "Unknown" or "1,200 JPY", a type conversion step may fail somewhere. Do not assume that all amounts are correct based solely on type information.

Furthermore, if CSV column names differ due to leading or trailing spaces, full-width and half-width characters, or visually similar characters, simple column name matching will fail. You must establish rules for data exchange regarding what constitutes the official column name in the first place.

Decision for inclusion in monthly processing

A good usageis to check for the presence of mandatory columns at the initial stage of importing the monthly file, reject data that does not meet the conditions, and allow the person in charge to check for discrepancies in input specifications.

A usage to avoidis to swallow everything with try ... otherwiseand proceed with processing while leaving columns blank even if they are missing. Unless it is operationally decided that "missing columns are acceptable," concealing anomalies makes it harder to notice aggregation errors.

By defaulting to an implementation that allows selection by column name even if the column order changes, you can reduce the impact of positional changes. However, also check the specific behavior of the connector, such as how duplicate column names are handled.

Sample on GitHub

• Power Query M Sample: Display Column Information, Check Missing Columns, and Determine Column Existence

check-schema.pq consolidates the three independent queries used in this article.Please paste each query into the Advanced Editor separately. This is not a script for pasting the three let ... in continuously as they are.

When testing on a physical machine, recording the Excel or Power BI version, the displayed TypeName, and the error details will result in reproducible verification records.

Referenced official Microsoft documentation

• Table.Schema

• Table.ColumnNames

• Table.HasColumns

• Table.FromRows

• List.Difference