2.1 Data Profiling in Power Query

Quick Answer: Power Query has three data profiling tools in the View tab: Column Quality (valid/error/empty percentages), Column Distribution (distinct/unique counts), and Column Profile (detailed statistics). Always switch to "entire dataset" profiling — the default only scans the first 1,000 rows.

Data profiling is the process of examining data to understand its structure, quality, and content before applying transformations. Power Query provides built-in profiling tools that are essential for making informed data cleaning decisions.

Enabling Data Profiling

Data profiling tools are found in the View tab of Power Query Editor:

View tab → Data Preview section → Check the boxes:
  ☑ Column Quality
  ☑ Column Distribution
  ☑ Column Profile

Critical Setting: By default, profiling is based on the first 1,000 rows. For accurate profiling, change this by clicking the status bar at the bottom of Power Query Editor and selecting "Column profiling based on entire dataset".

Column Quality

Column Quality adds a color-coded bar at the top of each column showing the percentage of:

What to look for:

• Columns with high error percentages → may need type changes or error handling
• Columns with high empty percentages → decide whether to filter, replace, or fill
• 100% valid doesn't mean the data is correct — just that it matches the expected type

Column Distribution

Column Distribution shows two key metrics as a histogram at the top of each column:

Interpreting the metrics:

• Distinct = Row Count → every value is different (likely a key column)
• Distinct = 1 → all values are the same (column may be unnecessary)
• Unique = Distinct → no duplicate values (good candidate for a primary key)
• Unique << Distinct → many values repeat (typical for category columns)

Example:

• A CustomerID column with 10,000 rows, 10,000 distinct values, and 10,000 unique values → perfect primary key candidate
• A Region column with 10,000 rows but only 5 distinct values → categorical column

Column Profile

Column Profile provides the most detailed analysis for the selected column. It appears in a panel below the data preview.

For Text Columns

For Numeric Columns

For Date Columns

Profiling Workflow

A systematic profiling approach:

1. Enable all three profiling tools and switch to entire dataset
2. Check Column Quality — flag columns with errors or high empty rates
3. Review Column Distribution — identify keys, categories, and anomalies
4. Examine Column Profile — deep-dive into suspicious columns
5. Document findings — note which columns need cleaning, type changes, or removal
6. Prioritize actions — fix errors first, then handle nulls, then optimize types

Reading the Value Distribution Bar

Beyond the headline percentages, the value distribution chart under Column Profile is the fastest way to spot dirty data. Hovering a bar shows the exact value and its count, which surfaces problems that summary statistics hide:

• Two bars for what should be one value — e.g. "USA" and "usa", or "New York" and "New York " (trailing space). This signals a standardization step (Trim, then Capitalize Each Word) is needed before the column can be used as a key or slicer.
• An unexpected spike at one value — often a placeholder like 0, -1, 9999, or "N/A" that should be treated as null.
• An outlier far from Min/Max — a single extreme value that may be a data-entry error and will distort averages and visuals.

Why "Entire Dataset" Matters

The default 1,000-row profiling sample is the single most-tested gotcha in this objective. If the first 1,000 rows happen to be clean but row 50,000 contains the errors, Column Quality will misleadingly report 100% valid. Always click the status-bar message and choose "Column profiling based on entire dataset" before making cleaning decisions — with the understanding that full profiling sends a heavier query to the source and may be slower on very large tables.

Exam Tip: Distinct counts every different value; unique counts only values that appear exactly once. When distinct = unique = row count, no value repeats — the textbook signature of a primary-key candidate.

On the Exam

The PL-300 frequently tests:

• Knowing where to find profiling tools (View tab in Power Query Editor)
• Interpreting Column Quality percentages (valid, error, empty)
• Distinguishing between distinct count and unique count
• Understanding the default 1,000-row profiling limitation
• Using profiling results to inform data cleaning decisions