7. Data Cleaning A–Z in Power Query
7.2 Data Profiling Tools
Start with a problem
Ravina loads an orders file and builds charts. Later she finds two in the Qty column and a delivery time of 240 minutes. Profiling would have shown this in one minute.
Explain simply
Data profiling shows problems before you fix anything: Column quality (valid/error/empty), Column distribution (distinct/unique) and Column profile (min, max, value counts).
Show the data
| Order ID | Qty | Amount | Delivery Mins |
|---|---|---|---|
| BLK-501 | 2 | 500 | 8 |
| BLK-502 | two | 300 | 12 |
| BLK-503 | 1 | N/A | 9 |
| BLK-504 | 3 | 450 | 240 |
| BLK-505 | 1 | 200 | -5 |
| BLK-506 | 2 | 350 | 11 |
About this data: This table uses columns: Order ID, Qty, Amount, Delivery Mins. This table uses columns: Column, Valid, Error. This table uses columns: Min, Max. This table uses columns: Problem found, Where, Fix in. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: pq_errors_outliers.csv (source: src/practice/).
Demonstrate
- Load
pq_errors_outliers.csv› Transform data. Set Qty, Amount and Delivery Mins to Whole Number. - View tab › tick Column quality, Column distribution, Column profile.
- Status bar › Column profiling based on entire data set.
-
Read the quality bars.
Your result should look like this:
Column Valid Error Qty 83% (5) 17% (BLK-502 "two") Amount 83% (5) 17% (BLK-503 "N/A") Delivery Mins 100% 0% -
Click Delivery Mins and read Column statistics.
Your result should look like this:
Min Max -5 240
Show the result
Your result should look like this (fictional sample):
| Problem found | Where | Fix in |
|---|---|---|
| Text in number column | Qty, Amount | 7.15 |
| Impossible delivery times (-5, 240) | Delivery Mins | 7.16 |
| Order ID distinct = unique = 6 | Order ID | no duplicates |
Explain what happened
Profiling turned silent problems into a to-do list, before any visual used the bad values.
Explore further
- Distinct = different values; unique = values that appear exactly once.
Detailed reference for this lesson:
Profiling (डेटाची तपासणी) tells you what is wrong before you start fixing things.
Steps in Power BI
- Open the Power Query Editor (Home › Transform data).
- On the View tab, tick Column quality, Column distribution and Column profile.
- In the status bar at the bottom-left, click Column profiling based on top 1000 rows and change it to Column profiling based on entire data set.
- Click a column header to see its Column statistics (count, error, empty, distinct, unique, min, max …) and Value distribution at the bottom.
- Hover over the quality bar under a header to see the exact counts; use the … menu there for quick actions such as Remove Errors or Remove Empty.
| Tool | What it shows | Example problem it reveals |
|---|---|---|
| Column quality | % Valid, % Error, % Empty (green/red/grey bar) | 3% of Delivery Time Mins are errors because some rows contain "NA" |
| Column distribution | Count of distinct and unique values with a mini histogram | Order ID shows distinct ≠ row count, so duplicates exist |
| Column profile | Full statistics and a value-distribution chart for the selected column | City has "Pune", "pune", "PUNE " as separate bars |
Distinct vs unique: distinct = number of different values; unique = number of values that appear exactly once. For a key column like Order ID in a header table, distinct should equal the row count.
Ravindra Bagale's Tip
Remember one thing: by default, profiling looks only at the top 1000 rows, so a column can look 100% valid when the errors are in row 45,000. Before you trust the numbers, click the status bar and switch to Column profiling based on entire data set. Switch back later if the editor becomes slow. Don't make this mistake!
Ravindra Bagale's Tip – मराठी
एक गोष्ट लक्षात ठेवा: default मध्ये profiling फक्त पहिल्या 1000 rows बघतं, त्यामुळे errors row 45,000 मध्ये असले तरी column 100% valid दिसू शकतो. आकड्यांवर विश्वास ठेवण्याआधी status bar वर click करा आणि Column profiling based on entire data set वर switch करा. Editor slow झाला तर नंतर परत बदला. ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
एक बात याद रखो: default में profiling सिर्फ़ पहली 1000 rows देखता है, इसलिए errors row 45,000 में हों तब भी column 100% valid दिख सकता है. आँकड़ों पर भरोसा करने से पहले status bar पर click करो और Column profiling based on entire data set पर switch करो. Editor slow हो जाए तो बाद में वापस बदल लो. यह गलती मत करना!
Practice task
Profile the Blinkit Orders query on the entire data set. Note down (a) which columns have empty values, (b) how many distinct cities appear, and (c) whether Order ID + Product ID is unique.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| 100% valid but errors appear later | Profiled only top 1000 rows | Switch to entire data set |
| No quality bars | View options off | Tick Column quality/distribution/profile |
| Valid 100% for Delivery Mins, so data is fine | Valid ≠ sensible | Check min/max in Column profile |
Practise independently
Practice task
What would the average delivery time be if you ignored the profile and used all 6 values?
Hints
- 8+12+9+240−5+11.
Solution
275 ÷ 6 = 45.8 minutes – clearly wrong; the valid average is 10.0 (7.16).
Think like a professional
Start every new dataset with a 5-minute profile and note findings – it saves hours later.
Revision card
Interview card