Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mtomova
Helper I
Helper I

Count number of columns in a table, excluding columns with specific column names in Power Query

Hi all,

 

I want to count the number of columns in a table, but excluding some columns with specific name.

 

This is what I have tried, but it's not working:

= Record.RemoveFields(Table.ColumnCount(#"MyTable"), {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"} )

 

 

This bit of the M code is working fine:

 

= Table.ColumnCount(#"MyTable")

 

It returns 30, this is how many columns I have in the table.

However, I want to count all columns excluding the columns with these names - "ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"

 

When I use the code I have pasted above, I get this error message:

mtomova_0-1737724154091.png

 

 

As a workaround I have done the below:

 

= Table.ColumnCount(#"MyTable") - 4

 

and it's working

 

Since I know that I want to remove 4 columns I am just performing the subtraction.

However, I am keen on learning how to do this using M code.

 

Thanks,

Maria

 

1 ACCEPTED SOLUTION
mtomova
Helper I
Helper I

Hey,

 

wow, I found a way as I kept playing with the code.

 

What I did is first, remove the columns that I don't want to count from the table:

 

= Table.RemoveColumns(#"MyTable"), {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"} )

 

This M code returns a table. Then I have enwrapped this code in a Table.ColumnCount():

 

= Table.ColumnCount(
Table.RemoveColumns(#"MyTable"), {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"} )

)

 

And I got exactly what I needed (:

 

I hope that this helps someone

View solution in original post

4 REPLIES 4
mtomova
Helper I
Helper I

Hey,

 

wow, I found a way as I kept playing with the code.

 

What I did is first, remove the columns that I don't want to count from the table:

 

= Table.RemoveColumns(#"MyTable"), {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"} )

 

This M code returns a table. Then I have enwrapped this code in a Table.ColumnCount():

 

= Table.ColumnCount(
Table.RemoveColumns(#"MyTable"), {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"} )

)

 

And I got exactly what I needed (:

 

I hope that this helps someone

Hi @mtomova, there are allways many ways:

= List.Count(List.Difference(Table.ColumnNames(#"Your Table Name"), {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"}))

or

= Record.FieldCount(Record.RemoveFields(#"Your Table Name"{0}, {"ColumnX",  "ColumnXP",  "ColumnAAA", "ColumnVVBVV"}))

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi,

 

thanks for providing different options!

 

Thanks,

Maria

Hi Maria, you're welcome 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.