Table.Profile

Table.Profile is a Power Query M function that generates a profile for each column in a table, providing various statistics. The function returns a table with information such as minimum, maximum, average, standard deviation, count, null count, and distinct count for each column.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.Profile(
   table as table,
   optional additionalAggregates as nullable list,
) as table

Argument	Attribute	Description
table		The table to generate statistics for.
additionalAggregates	optional	A list of nested lists. Each list corresponds to an aggregate column to add to the table. The list should contain: { columnName, function, aggegrationFunction }

Description

Returns a profile for the columns in table.

The following information is returned for each column (when applicable):

• minimum
• maximum
• average
• standard deviation
• count
• null count
• distinct count

You can optionally provide your own aggregates. Doing that adds additional columns to the table.

Examples

Let’s say you have the below dataset. It contains different types of values ranging from text to numbers and lists.

Applying the Table.Profile function to this table returns its statistics.

Table.Profile( Source )

The columns Average and StandardDeviation return an error. The reason is that the original Num column contains a cell with an empty string (“”) in row 11. Performing a mathematical operation on this cell is invalid.

The above example returned the default profile information as described above. Additionally, the additionalAggregates argument allows you to add your own custom columns. To add a “NonEmpty” column that counts how many cells are not empty in each column you can write below:

Table.Profile(
  Source, 
  {
    {
      "NonEmpty", 
      each true, 
      each List.NonNullCount(
             List.Select(
               List.Transform( _, each try Text.Trim( Text.Clean( _ ) ) otherwise _ ), 
               each _ <> "")
           )
    }
  }
)

This returns the same table, but with a new column for “NonEmpty”.

Now let’s say you want to select a value from the above list. You might try to filter the “NullCount” column to be equal to 2 using:

// Output: Expression.Error: The field 'NullCount' of the record wasn't found.
Table.SelectRows( myTable , each ([NullCount] = 2 ))

To prevent this error, you should first wrap the output of Table.Profile in a function like Table.StopFolding or Table.Buffer.

// Output: Rows where the "NullCount" column equals 2
Table.SelectRows( Table.StopFolding( myTable), each ([NullCount] = 2 ) )

Related articles

Learn more about Table.Profile in the following articles:

• Table.Profile And Its Unknown Second Parameter
  The article explores the second parameter of the Table.Profile function, explaining its use for adding custom columns and handling data types in table profiling. » Read more

Related functions

Other functions related to Table.Profile are:

• Table.HasColumns
• Table.IsDistinct
• Table.IsEmpty
• Table.MatchesAllRows
• Table.MatchesAnyRows
• Table.Schema
• Tables.GetRelationships

Contribute » | Contributors: Rick de Groot, Melissa de Korte, Štěpán Rešl
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-profile