"Sales are down 18% in the West region. Your bar chart proves it."
"Wait — which 'West'? The data has 'West', 'west', and 'WEST' as three separate values."

That is not a visualisation problem. It is a data quality problem — and it is invisible until you look for it. Power BI's three data profiling tools in Power Query Editor exist to find these problems before they reach your report. Five minutes of profiling saves five hours of wrong decisions.

Quick Answer Power Query Editor has three profiling tools under the View tab → Data Preview group: Column Quality (shows valid/error/empty %), Column Distribution (shows value spread and distinct/unique counts), and Column Profile (full statistics: min, max, average, std dev, most/least frequent values). Enable all three before writing any transformation step.
  • Column Quality — instant data health check per column (green=valid, red=error, grey=empty)
  • Column Distribution — histogram + distinct count vs unique count
  • Column Profile — full statistics panel for a selected column
  • Default profiling covers only the first 1,000 rows — change this for large tables
🏥 The X-Ray Analogy

A surgeon doesn't operate without an X-ray. The X-ray reveals what is invisible to the naked eye — a fracture, a misaligned joint, a hidden anomaly. Data profiling is your X-ray. Your data may look fine in the preview grid, but Column Profile will show you that your Revenue column has 47 null values, your Region column has 6 distinct values when there should be 4, and your Date column has a min of 1900-01-01 (a classic Excel date error). You find these now, before a wrong chart misleads a board meeting.

🔍 How to Enable All Three Features

All three tools live in the same place — the View tab of the Power Query Editor ribbon:

  1. Open Power Query Editor — click Transform Data from the Home ribbon in Power BI Desktop
  2. Click the View tab in the Power Query ribbon
  3. In the Data Preview group, check: Column quality, Column distribution, and Column profile
  4. Quality bars appear above each column, distribution histograms below, profile panel at the bottom when you click a column
Critical: By default, Power Query profiles only the first 1,000 rows. For large tables, click the status bar at the bottom and switch to "Column profiling based on entire data set". Errors in row 50,000 will not appear in the default 1,000-row sample.
✅ Column Quality

Shows the percentage of valid, error, and empty values for each column as a horizontal bar directly above the column data.

  • Instantly spot which columns have missing or dirty data
  • Green = valid, red = errors, grey = empty
  • Hover over the bar to see exact counts and percentages
  • Accessible via View → Column quality
When to use Quick data validation pass — identify columns that need cleaning before you start building transformations.
📊 Column Distribution

Visually displays the value distribution across a column as a small histogram, plus distinct and unique value counts.

  • Distinct count = number of different values (including duplicates counted once)
  • Unique count = values that appear exactly once
  • The histogram shows how data is spread — spot skewed distributions instantly
  • Helpful for verifying categorical columns (e.g. should Region have exactly 4 values?)
When to use Spotting outliers, duplicate issues, and verifying that categorical columns contain expected values only.
🧪 Column Profile

The most detailed of the three — provides a full statistical breakdown of a selected column in a dedicated panel at the bottom of Power Query Editor.

  • Count, empty count, distinct count, unique count
  • Min, max, average, standard deviation (for numeric columns)
  • Most frequent and least frequent values with counts
  • A value distribution chart for the selected column
  • Data type and any detected errors shown clearly
When to use Deep-dive analysis of a specific column — understand its range, outliers, and value distribution before deciding how to transform it.
🏢 Enterprise Example — 500-Store Retailer Data Audit

A retail chain analyst loads a SQL Server sales table (2M rows) into Power Query before building a regional dashboard. She enables all three profiling tools and finds:

ColumnProfiling FindingAction Taken
RegionColumn Distribution: 6 distinct values — expected 4. Profile reveals "North East" and "NorthEast" as separate entries.Replace Values in Power Query to standardise to 4 canonical names.
RevenueColumn Quality: 3% errors. Profile shows min = -99999 (clearly invalid).Remove Error rows; filter out rows where Revenue < 0.
SaleDateColumn Profile: min = 1900-01-01 (Excel date zero error). 847 rows affected.Filter rows where SaleDate < 2020-01-01 before loading.
StoreIDColumn Quality: 100% valid. Distribution shows all 500 expected stores present.No action needed — confirmed clean.

Without profiling, all four issues would have silently entered the data model and produced wrong visuals. The whole audit took 8 minutes.

⚠️ 3 Profiling Mistakes That Let Bad Data Through
  1. Profiling only the first 1,000 rows and calling the data clean. The default sample is 1,000 rows. In a 2M-row table, that is 0.05% of your data. Data quality issues — especially in operational tables — tend to cluster in older records or recent error batches. Always switch to "entire data set" profiling before signing off on data quality.
  2. Ignoring the Distinct vs Unique count difference. A Region column with 4 distinct values is fine. But if unique count equals distinct count equals row count, the column is a unique identifier — not a category dimension. Distinct = how many different values exist. Unique = how many appear exactly once. These serve different diagnostic purposes.
  3. Closing Power Query without acting on the profiling findings. Profiling is only useful if it leads to transformation steps. After you identify a dirty column, add the fix as an Applied Step immediately. Do not plan to "fix it later" — later never comes, and the bad data reaches the model.
✅ Which Tool for Which Job
ToolWhat It ShowsBest Used For
✅ Column QualityValid / Error / Empty %Quick data health check across all columns
📊 Column DistributionValue spread, distinct & unique countsSpotting unexpected categories, outliers, duplicates
🧪 Column ProfileFull stats: min, max, avg, std dev, frequencyDeep-dive on a specific column before transforming
  • Enable all three from Power Query Editor → View tab → Data Preview group
  • Switch to "entire data set" profiling for tables larger than 1,000 rows
  • Profile before writing any transformation step — it tells you what to fix
  • Column Distribution's Distinct vs Unique counts serve different diagnostic purposes
  • Always act on profiling findings immediately — add an Applied Step before closing Power Query
🧠 Check Your Understanding

Q1. You enable Column Distribution on a ProductCategory column and see: Distinct = 12, Unique = 9. What does this tell you, and is it a problem?

Show Answer

Distinct = 12: there are 12 different category names in the column.
Unique = 9: 9 of those names appear exactly once (they have no duplicates).
This means 3 category names appear more than once — which is expected for a category dimension (most categories will appear in many rows). This is normal. It would only be a problem if you expected all 12 categories to be unique identifiers (like a primary key), where you'd want Distinct = Unique = row count.

Q2. Column Quality on your Revenue column shows: Valid 94%, Empty 3%, Error 3%. You need clean revenue data for a financial report. What two transformation steps should you add in Power Query?

Show Answer

Step 1 — Handle errors: Right-click the Revenue column → Remove Errors (or Replace Errors with null/0 depending on business rule). This removes/replaces the 3% error rows.
Step 2 — Handle nulls: Use Remove Rows → Remove Blank Rows, or filter where Revenue is not null. The 3% empty values are likely missing data — check with stakeholders whether to remove those rows or substitute a default value (e.g., 0 for missing sales).

Q3. You are profiling a 5M-row orders table. Column Profile on the OrderDate column shows min = 1899-12-30. You know all orders are from 2020 onwards. What happened and how do you fix it?

Show Answer

1899-12-30 is Excel's serial date zero — it represents the number 0 stored as a date, which Excel renders as 30 December 1899. This typically happens when Excel exported rows where the date cell was empty or zero.
Fix: in Power Query, add a filter step: Filter Rows → OrderDate is after 2020-01-01 (or whatever your valid range start is). This removes all rows with the Excel date zero error before loading into the model.

Up Next in Chapter 4 4.3 Data Transformation — splitting columns, merging tables, unpivoting data, and building reusable transformation steps in Power Query. Read 4.3 →
📚 Resources