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

[mtomova]

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:

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

[mtomova · Accepted solution]

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

[dufoq3]

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"}))

[mtomova]

Hi,

thanks for providing different options!

Thanks,

Maria

[dufoq3]

Hi Maria, you're welcome 😉