Table.Profile and its unknown second parameter

A couple of  weeks ago I could finally publish my post about tables in M – how, when and why. Under „Other special table functions“ I mentioned the function Table.Profile() as a function to get meta information about tables. During my research I noticed that this function accepts a second optional parameter which is not documented anywhere. Also other posts about this function, which colleagues from the community had already written, did not mention this parameter (at least none I could find). This made me curious, so I reached out to the Power Query dev team.

Syntax of the function

The syntax of the function is as follows: Table.Profile(table as table, optional additionalAggregates as nullable list) as table. The first mandatory parameter is the table whose profile is to be created. The second parameter is a puzzle. Let’s take a closer look at this one.

Second parameter «additionalAggregates»

This argument is expected as a list of lists, like this: {{},{}}. Each of these inner lists represents a new calculated profile column and consists of three items:

1. item: Output column name as text
2. item: typecheck function as function
3. item: aggregation function as function

Let’s take a detailed look.

Output column name

This is just the name of the new profile column, which of course should reflect the content of the column.

Typecheck function

The typecheck function is expected to be something like each Type.Is(_, type any). While the keyword „each“‚ refers to the current record in the profiling table, the underscore „_“ refers not – as you might expect – to the current record, but to the column of the source table, whose name can be found in the current record in the first column „Column“. So the underscore „_“ doesn’t return a record, but a list. This information will also be important for the third item of the parameter additionalAggregates: the aggregation function.

Aggregation function

If the second parameter – the typecheck function – returns true, then the aggregation function kicks in. Otherwise the output of the aggregation function will be null.

So how could examples of the aggregation function look like?

Aggregation function – example #1

= Table.Profile(Table.FromRows({{1, 2}, {3, null}}, type table [A=any, B=number]), {{"New profile column", each Type.Is(_, type number), each List.Average(_)}})

What happens in the profile table?

• Column A: This column is of type any. My function is explicitly looking for type number (Type.Is(_, type number)), so the function List.Average() is not used for column A. The value null is returned accordingly.
• Column B: This column is of type number. My function is explicitly looking for type number (Type.Is(_, type number)), so the function List.Average() can kick in. The value 2 is returned as the average of 2 and null is 2.

Aggregation function – example #2

= Table.Profile(Table.FromRows({{"ABC", 2}, {"C", 3}}, type table [A=text, B=any]), {{"AverageCharactersCount", each Type.Is(_, type text), each List.Average( List.Transform(_, each Text.Length(_))) }})

What happens in the profile table?

• Column A: This column is of type text. My function is explicitly looking for type text (Type.Is_, type text), so the function List.Average( List.Transform(_, each Text.Length(_))) is used for column A. The returned value is 2 as the average text length of „ABC“ and „C“ is 2. 
• Column B: This column is of type any. My function is explicitly looking for type text (Type.Is_, type text), so the function List.Average( List.Transform(_, each Text.Length(_))) doesn’t kick in, but returns null.

When using the UI to assert types

When you change a data type using the UI, the M function Table.TransformColumnTypes() is used behind the scenes to do that job. What you should always keep in mind when using this function is, that it always returns a nullable data type. This is something I forgot, even if it was a central message of this of my own recent posts. Why is this important in view of the last post? Take a look at the following examples:

In the first screenshot I use ascribing types and my custom column to Table.Profile() just works.

In the second screenshot I use the UI/ Table.TransformColumnTypes() to assert the type and my function returns null, where I expected the value 2. Why does that happen. As I said, in my example Table.TransformColumnTypes() returns a nullable text type and Type.Is(type nullable text, type text) = false. This is why I get back null.

To fix this behavior, I have to check for type nullable text, instead of type text:

The moment you do it right, it’s running. 🙂

Greetings from Germany,

Lars