Power QueryExercises

Profile columns before you trust them

Build a quick profile of a table's columns and read what it says about data quality.

~9 min read · intermediate · Module 8: Profiling, errors and validation

By Tahir Nas · Examples run in our simulator · Updated

Before this: Find, remove and replace errors

Profiling means summarising each column before relying on it: how many values are missing, how many are distinct, what the smallest and largest are. Ten seconds with a profile catches problems that would take an hour to trace back from a wrong chart.

A profile you can build anywhere

Each column is a list (Source[Qty]), so list functions answer the profile questions. Building one row per column with List.Transform gives a profile table that refreshes with the data:

let
    Source = #table({"OrderID", "Region", "Qty"}, {
        {"A-1", "North", 2}, {"A-2", null, 5}, {"A-3", "North", null}, {"A-3", "South", 40}
    }),
    Profile = Table.FromRecords(List.Transform(Table.ColumnNames(Source), (c) => [
        Column = c,
        Rows = Table.RowCount(Source),
        Nulls = Table.RowCount(Source) - List.NonNullCount(Table.Column(Source, c)),
        Distinct = List.Count(List.Distinct(Table.Column(Source, c))),
        Min = List.Min(Table.Column(Source, c)),
        Max = List.Max(Table.Column(Source, c))
    ]))
in
    Profile

Result

ColumnRowsNullsDistinctMinMax
OrderID403A-1A-3
Region413NorthSouth
Qty414240

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

List.Distinct counts null as a value, so Region's three distinct values are North, null and South.

Reading the profile

  • OrderID: 4 rows but 3 distinct values, so an ID repeats. Is A-3 a duplicate or two lines of one order?
  • Region: one null. Decide whether that's 'unknown region' or a data entry error.
  • Qty: one null and a maximum of 40 when the others are single digits. Check whether 40 is real or a typo for 4.

None of these is necessarily wrong. A profile raises questions; the business context answers them. What it does guarantee is that you ask the questions before someone else finds the answer in your report.

When to profile

  • Right after importing a new source, before writing any transformations.
  • After merges, to see whether row counts or nulls jumped.
  • On each refresh of a critical report, as a small check query (next lesson).

Where to find this in Excel

Excel for Windows
View > Column quality, Column distribution and Column profile (Data Preview group)
What you see
Charts under the headers show valid, error and empty shares and value counts; Column profile adds statistics below the preview.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Using the data profiling tools (Microsoft Learn).

Common mistakes

  • Judging a column by the preview's first rows when the problem is further down.
  • Reading 'distinct = rows' as proof that a key is unique, without checking for null.
  • Treating every outlier as an error and deleting it without asking the data owner.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: View → Column quality, Column distribution and Column profile show this visually. They profile the first 1,000 rows unless you switch the status bar option to the entire data set.
  • Table.Profile returns a built-in profile table in both hosts; the custom version here works anywhere and shows exactly how each figure is calculated.
  • The simulator has no visual profiling panes; build profiles with list functions as shown.
Functions in this lesson:List.NonNullCountList.DistinctList.MinList.MaxTable.ColumnNames

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Errors and validation quiz