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
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.