Prepare the data · Topic 3 of 8
Profiling Data and Column Statistics
PL-300 data profiling: use column quality, column distribution and column profile in Power Query, read data statistics and check column properties.
- 4 min
- The three views are column quality, column distribution and column profile.
- Quality shows valid, error and empty percentages. Distribution shows distinct and unique counts.
- Profiling uses the top 1,000 rows by default. Change it to the entire data set in the status bar.
What it is
Profiling means looking at the data before you change it. Power Query has three profiling views that show how many values are valid, empty or wrong, how many distinct values a column has and how the values are spread. You also check column properties, such as the data type and name. Good profiling finds problems early, so you clean the right things.
How it works
The three profiling views
On the View tab of the Power Query Editor, tick the options below. They appear above each column.
| View | What it shows |
|---|---|
| Column quality | The percentage of Valid, Error and Empty values |
| Column distribution | A bar chart of frequency, with the count of distinct values and unique values |
| Column profile | Full statistics for the selected column and a value distribution chart |
Distinct counts every different value once. Unique counts only the values that appear exactly once. A key column should have the same distinct and unique count, and no empties.
What column profile reports
Select a column, and the profile shows statistics such as count, errors, empty, distinct, unique, minimum, maximum, average, standard deviation and median. For text columns it shows the most common values. For numeric columns you also see whether values are even or odd. This is where you spot outliers: a maximum of 9,999,999 in a quantity column, or a minimum of -1 where negative values should not exist.
Sample size
By default, profiling uses the top 1,000 rows only. Rows beyond that are not in the statistics. In the status bar at the bottom left, switch to Column profiling based on entire data set to profile everything. It is slower on large sources, but an error on row 50,000 will otherwise be invisible. Quality percentages and the distribution chart follow the same setting.
Other ways to read statistics
- In the Data view of Power BI Desktop, the status bar shows the row count of the table and the distinct count of the selected column.
- The M function Table.Profile returns a table of statistics, and Table.Schema returns the column names, types and properties.
- The Text view options can show whitespace characters, which reveal leading and trailing spaces that look like normal text.
Column properties
Properties describe a column. In the Power Query Editor, the Query Settings pane lists the applied steps, and the column header shows the data type icon. In the model, the Properties pane shows the name, description, data type, format, summarization, sort order and whether the column is hidden. Check these early. A numeric ID typed as a whole number will be summed by default, which makes no sense, so set Summarize by to Don't summarize.
Types of problems profiling finds
| Finding | Likely problem |
|---|---|
| Empty above a few percent | Missing source values, or blank rows |
| Error percentage above zero | A type conversion failed, or a source formula returned an error |
| Distinct count much higher than expected | Typos, extra spaces or mixed casing |
| Distinct count equals row count | A candidate key, or a high cardinality column that may be removed |
| Odd minimum or maximum | Outliers or placeholder values such as 9999 or 1900 |
A worked scenario
An analyst loads a customer table of 80,000 rows. Column quality for Country shows 99 percent valid, and distribution shows 31 distinct values, although the company serves 25 countries. She switches to profiling on the entire data set, and the distinct count rises to 38. The extra values are "UK", "U.K." and "United Kingdom ", with a trailing space. Profiling told her what to fix before she touched anything.
What to remember for the exam
- The three views are column quality, column distribution and column profile.
- Quality shows valid, error and empty percentages. Distribution shows distinct and unique counts.
- Profiling uses the top 1,000 rows by default. Change it to the entire data set in the status bar.
- Unique means appears exactly once. Distinct means each different value counted once.
- Table.Profile and Table.Schema expose statistics and properties in M.
If a question says a problem was missed because only part of the data was checked, the answer is the profiling sample size.
How the exam asks about this
- "You need the percentage of empty values in a column." Column quality.
- "You need the number of values that appear only once." Column distribution, unique count.
- "Profiling does not show errors beyond row 1,000." Profile the entire data set.
- "Find the minimum, maximum and standard deviation." Column profile.
Common mistakes
Trusting a 1,000 row sample. Errors later in the table stay hidden.
Confusing distinct and unique. Distinct counts each value once, unique counts values that occur once.
Looking only at values. Check the data type too.
Cleaning before profiling. You may remove the wrong things.
Check yourself
What is the default profiling sample?
The top 1,000 rows.
Which view shows valid, error and empty percentages?
Column quality.
What does the unique count measure?
The number of values that appear exactly once.
Topic 3 of 8 in Prepare the data
Test yourself on Prepare the data
The full Microsoft PL-300 question bank has 657 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.