Table.ColumnCount

D

T

The M Code Behind the Power Query M function Table.ColumnCount

Table.ColumnCount is a function that returns the number of columns in a given table. In this article, we will explore the M code behind this function and how it works.

Understanding the M Language

Before diving into the M code behind Table.ColumnCount, it’s important to understand the M language. M is the scripting language used in Power Query to manipulate and transform data. It’s a functional language that is similar to other programming languages such as Python and R.

M code is made up of expressions and statements. An expression is a value or a calculation that returns a value. A statement is a group of expressions that are executed in sequence.

The M Code Behind Table.ColumnCount

To understand the M code behind Table.ColumnCount, we will need to create a sample table. In Power Query, go to the Home tab and click on the “From Table” button. This will open a dialog box where you can enter your data.

For this example, we will create a table with three columns:

| Column 1 | Column 2 | Column 3 |

|———-|———-|———-|

| Value 1 | Value 2 | Value 3 |

| Value 4 | Value 5 | Value 6 |

| Value 7 | Value 8 | Value 9 |

Once you have created your table, go to the “Advanced Editor” in Power Query. This will open the M code for your table.

The M code for our sample table should look like this:


let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIxVkjNyclWJVYyNzLXMjQwNDC3MDfRyM/Nz83My0xNTI3MLQwNjIwM7QwNzAzsDGyMDDWNTU3sDQys7Ty1s0tKLAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})

in

#"Changed Type"


The first line of the code creates a variable called “Source” that contains our table. The second line transforms the data types of each column to text.

To get the number of columns in our table, we can use the Table.ColumnCount function. The M code for this function is:


Table.ColumnCount(Source)


This code will return the number of columns in our table, which is 3.

Table.ColumnCount is a useful function in Power Query that allows you to quickly get the number of columns in a given table. The M code behind this function is relatively simple and can be easily understood with a basic understanding of the M language.

In this article, we explored the M code behind Table.ColumnCount and how it works. We also discussed the M language and how it is used in Power Query to manipulate and transform data. Hopefully, this article has provided you with a deeper understanding of Power Query and its capabilities.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)