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

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

Other functions related to Table.Profile are:

BI Gorilla Blog

Last update: August 17, 2023 | Contribute » | Contributors: Rick de Groot, Melissa de Korte
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-profile
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.