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
ProfileResult
| Column | Rows | Nulls | Distinct | Min | Max |
|---|---|---|---|---|---|
| OrderID | 4 | 0 | 3 | A-1 | A-3 |
| Region | 4 | 1 | 3 | North | South |
| Qty | 4 | 1 | 4 | 2 | 40 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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.
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