Table.HasColumns Function in Power Query M (5 Easy Examples)

Table.HasColumns checks whether a table contains one or more named columns and returns true or false. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to confirm that a column exists before you reference it, or guard a step against a column that might be missing, this is the function you reach for.

Table of Contents

Syntax of Table.HasColumns Function

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

where

table (required, table). The table to check.
columns (required, any). A single column name as text, or a list of column names. With a list, the result is true only when every listed column is present.

Returns: a logical value, either true or false. It is true when the table holds the named column(s) and false when at least one is missing.

In plain terms, you hand it a table and the column name (or list of names) you care about, and it tells you whether they are there.

Example 1: Check whether a single column exists

Say you have a GymClasses table and want to confirm it has an Instructor column.

Here is the starting data:

ClassName	Instructor	Capacity
Spin	Maya	20
Yoga	Raj	15
Boxing	Leo	12

Check for the column by name:

let
    Source = Excel.CurrentWorkbook(){[Name="GymClasses"]}[Content],
    Result = Table.HasColumns(Source,"Instructor")
in
    Result

Result: true

The Instructor column is present, so the function returns true.

Example 2: Get false when a column is missing

Using the same gym schedule, check for a column the table does not have.

Here is the starting data:

ClassName	Instructor	Capacity
Spin	Maya	20
Yoga	Raj	15
Boxing	Leo	12

Check for a Duration column that was never added:

let
    Source = Excel.CurrentWorkbook(){[Name="GymClasses2"]}[Content],
    Result = Table.HasColumns(Source,"Duration")
in
    Result

Result: false

There is no Duration column, so you get false instead of an error.

Example 3: Check several columns at once

Pass a list to confirm that more than one column is present.

Here is a BookStock inventory table:

Title	Author	Genre	InStock
Dune	Herbert	SciFi	4
It	King	Horror	2
Emma	Austen	Classic	6

Check that both Title and Genre exist:

let
    Source = Excel.CurrentWorkbook(){[Name="BookStock"]}[Content],
    Result = Table.HasColumns(Source,{"Title","Genre"})
in
    Result

Result: true

Both columns are in the table, so the list check returns true.

Example 4: A list returns false if one column is missing

This is the gotcha most people hit. With a list, every column must be present.

Here is the same book inventory:

Title	Author	Genre	InStock
Dune	Herbert	SciFi	4
It	King	Horror	2
Emma	Austen	Classic	6

Check for Title and a Price column that does not exist:

let
    Source = Excel.CurrentWorkbook(){[Name="BookStock2"]}[Content],
    Result = Table.HasColumns(Source,{"Title","Price"})
in
    Result

Result: false

Title is there but Price is not, and one miss makes the whole list false.

Example 5: Column names are case-sensitive

The name you pass must match the column’s casing exactly.

Here is a SupportTickets table with a Status column:

TicketID	Priority	Status
REF-101	High	Open
REF-102	Low	Closed
REF-103	Medium	Open

Check for status in lowercase:

let
    Source = Excel.CurrentWorkbook(){[Name="SupportTickets"]}[Content],
    Result = Table.HasColumns(Source,"status")
in
    Result

Result: false

The column is Status, so the lowercase status does not match and you get false.

Things to keep in mind with Table.HasColumns

Matching is case-sensitive. "status" does not find a Status column (Example 5). Match the exact casing, or normalize headers first with Table.TransformColumnNames. The same casing rule trips people up with Table.SelectColumns.
A list needs every column present. One missing name makes the whole call false (Example 4). To test whether any one of several columns exists, check them one at a time and combine with or, or feed the names to List.Contains.
The first argument must be a table. Passing a list or record throws Expression.Error: We cannot convert a value of type List to type Table. Reference the table itself, not a column.
It never errors on a missing column. That is the point: it returns false so you can branch on it. Use it as the test in an if before a step that would otherwise fail on the absent column.

Common questions about Table.HasColumns

How do I add a column only when it is missing?

Wrap the function in an if: if Table.HasColumns(Source,"Notes") then Source else Table.AddColumn(Source,"Notes",each null). The new column is added with Table.AddColumn only when the check is false.

What is the difference between Table.HasColumns and Table.ColumnNames?

Table.HasColumns answers a yes/no question about specific names. Table.ColumnNames returns the full list of column names, which you would then search yourself.

List of All Power Query Functions

Related Power Query Functions / Articles: