Table.Profile

Updated on

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
ArgumentAttributeDescription
tableThe table to generate statistics for.
additionalAggregatesoptionalA 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.

Table.Profile dataset in Power Query

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

Table.Profile( Source )
Table.Profile statistics in Power Query M

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”.

Table.Profile with additional aggregates in Power Query M

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

Learn more about Table.Profile in the following articles:

Other functions related to Table.Profile are:

BI Gorilla Youtube Channel

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