Table.ColumnCount Function in Power Query M (4 Easy Examples)

Table.ColumnCount returns the number of columns in a table as a whole number. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to know how many columns a table has, whether to check the shape of a source or to confirm a transform added or dropped the right fields, this is the function you reach for.

Table of Contents

Syntax of Table.ColumnCount Function

Table.ColumnCount(table as table) as number

where

table (required, table). The table whose columns you want to count.

Returns: a number, the count of columns in table. An empty table with defined columns still returns that column count.

In plain terms, you hand it a table and it tells you how many columns are in it.

Example 1: Count the columns in a table

You have a Trips query and want to know how many columns it has.

Here is the starting data:

Route	Miles	Driver
North Loop	42	Asha
Harbor Run	18	Devon
Hill Climb	27	Mira

Point Table.ColumnCount at the source:

let
    Source = Excel.CurrentWorkbook(){[Name="Trips"]}[Content],
    Result = Table.ColumnCount(Source)
in
    Result

Result: 3

The table has Route, Miles, and Driver, so the count is 3.

Example 2: Confirm a step added a column

You start with two columns and use Table.AddColumn to add a Reorder flag. You want to verify the count went up.

Here is the starting data:

SKU	OnHand
WID-100	12
WID-200	7
WID-300	0

Add the column, then count:

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory"]}[Content],
    Added = Table.AddColumn(Source,"Reorder",each [OnHand] < 5),
    Result = Table.ColumnCount(Added)
in
    Result

Result: 3

The two original columns plus the new Reorder column give a count of 3.

Example 3: Check the count after removing columns

You have five columns and drop two of them. You want to confirm three remain.

Here is the starting data:

RespID	Age	City	Score	Notes
R-01	34	Denver	8	none
R-02	41	Austin	6	follow up
R-03	29	Reno	9	none

Remove Notes and City, then count what is left:

let
    Source = Excel.CurrentWorkbook(){[Name="Survey"]}[Content],
    Trimmed = Table.RemoveColumns(Source,{"Notes","City"}),
    Result = Table.ColumnCount(Trimmed)
in
    Result

Result: 3

Five columns minus the two you removed leaves RespID, Age, and Score, a count of 3. The same check works if you keep columns with Table.SelectColumns instead.

Example 4: Count columns in an empty table

A table can have no rows but still have columns. Table.ColumnCount counts the columns, not the rows.

let
    Source = #table(type table [Code = text,Amount = number],{}),
    Result = Table.ColumnCount(Source)
in
    Result

Result: 2

The #table has Code and Amount defined but zero rows, so the count is still 2.

Things to keep in mind with Table.ColumnCount

It counts columns, not rows. For a row count use Table.RowCount. An empty table still returns its column count (Example 4).
The argument must be a table. A list or record throws Expression.Error: We cannot convert a value of type List to type Table. Wrap or convert it to a table first.
It reflects the table at that step. The count is whatever the table looks like at the step you call it on, so add and remove steps change the result (Examples 2 and 3).
For the actual column names, use Table.ColumnNames. It returns the list of headers, and you can wrap that in List.Count to get the same number a different way.

Common questions about Table.ColumnCount

What is the difference between Table.ColumnCount and Table.RowCount?

Table.ColumnCount returns how many columns a table has. Table.RowCount returns how many rows. They are the two dimension helpers for a table.

How do I get the column count when the editor caps the preview on a wide table?

Table.ColumnCount reads the real column count regardless of what the preview shows, so it is reliable even on tables with hundreds of columns.

List of All Power Query Functions

Related Power Query Functions / Articles: