Downloads
Power Query MTable: headers and shapeIntermediate

Table.Profile

Returns a table of statistics about each column, such as counts of blank and distinct values.

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

Returns: A table with one row for each column of the input.

What it does

Table.Profile gives a quick statistical summary of each column. It shows how many values are blank, how many are different from each other, and the minimum and maximum where they make sense. Use it to check a new export before you rely on it. It is a checking tool, so keep it in a separate query and not in the report.

Syntax and arguments

Example

Before a new bank export is loaded into the monthly report, the file is profiled in a separate query. The preparer opens it to see which columns are mostly blank.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Quality = Table.Profile(Source)
in
    Quality

Load this query to a worksheet or leave it as a connection-only query while checking the file. The report query is not changed.

Example: a second, different use

A receivables export is profiled to see how many distinct customers appear, which helps check that customer names are consistent.

let
    Source = Excel.CurrentWorkbook(){[Name = "ARExport"]}[Content],
    Profiled = Table.Profile(Source)
in
    Profiled

Things to watch

In business terms

Before a reconciliation or a model is built on an export, a profile shows the blanks, the duplicates and the odd values. It is a quick data quality check that the reviewer can point to.

Related functions

All Power Query M functions · View as Markdown · M basics