7. Data Cleaning A–Z in Power Query
7.2 Data Profiling Tools
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
Ek goshta lakshat theva: 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. Chuk karu naka!
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.