ExamFinch.com My Study

Study guide

2.1 Data Profiling in Power Query

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

2.1 Data Profiling in Power Query

Key Takeaways Explain This Topic AI

Data profiling tools include Column Quality, Column Distribution, and Column Profile in the View tab.

Column Quality shows percentages of Valid, Error, and Empty values per column.

Column Distribution displays distinct count and unique count, helping identify potential key columns.

Column Profile provides detailed statistics (min, max, average, standard deviation) and value distribution for the selected column.

By default, profiling is based on the first 1,000 rows — change to 'Column profiling based on entire dataset' for accuracy.

Last updated: June 2026

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:

  • Indicator Color Meaning
  • Valid Green Values conform to the column's data type
  • Error Red Values that cannot be resolved (type mismatches, division by zero)
  • Empty Gray Null or blank values

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:

  • Metric Definition Use Case
  • Distinct Total number of different values Understanding cardinality
  • Unique Values that appear exactly once Identifying potential keys

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

  • Statistic Description
  • Count Number of non-null values
  • Error Number of error values
  • Empty Number of null/blank values
  • Distinct Number of unique values
  • Unique Values appearing exactly once
  • Min Length Shortest text value
  • Max Length Longest text value
  • Value Distribution Bar chart of value frequencies
  • For Numeric Columns
  • Statistic Description
  • Count Number of non-null values
  • Error Number of error values
  • Empty Number of null/blank values
  • Min Smallest value
  • Max Largest value
  • Average Arithmetic mean
  • Standard Deviation Measure of data spread
  • Even/Odd Count Distribution of even and odd values
  • Zero Count Number of zero values
  • For Date Columns
  • Statistic Description
  • Min Earliest date
  • Max Latest date
  • Count Number of non-null dates
  • Value Distribution Timeline visualization of date frequencies
  • Profiling Workflow

A systematic profiling approach:

  • Enable all three profiling tools and switch to entire dataset
  • Check Column Quality — flag columns with errors or high empty rates
  • Review Column Distribution — identify keys, categories, and anomalies
  • Examine Column Profile — deep-dive into suspicious columns
  • Document findings — note which columns need cleaning, type changes, or removal
  • 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.

Profiling tool Best question it answers

Column Quality What share of values are valid / error / empty?

Column Distribution Is this a key, a category, or a constant?

Column Profile What are the exact statistics and value frequencies?

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