7. Data Cleaning A–Z in Power Query
7.16 Outliers and Invalid Values
Start with a problem
The average delivery time card shows 45.8 minutes. One row says 240 minutes and another says −5. Neither is possible for 10-minute delivery.
Explain simply
Outliers can be valid numbers that make no business sense. Agree a valid range (here 1–120 minutes), then flag invalid rows with a conditional column and exclude them from KPIs.
Show the data
Before:
| Order ID | Delivery Mins |
|---|---|
| BLK-501 | 8 |
| BLK-502 | 12 |
| BLK-503 | 9 |
| BLK-504 | 240 |
| BLK-505 | -5 |
| BLK-506 | 11 |
About this data: This table uses columns: Order ID, Delivery Mins. This table uses columns: Measure, Value. This table uses columns: Order ID, Qty, Delivery Time Mins, Status. This table uses columns: Order ID, Data Quality. 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
-
Add Column › Conditional Column
DQ: if Delivery Mins < 1 then "Invalid"; else if Delivery Mins > 120 then "Too long"; else "OK".Your result should look like this:
DQ OK, OK, OK, Too long, Invalid, OK -
Close & Apply. Create
Avg Delivery (valid) = CALCULATE(AVERAGE(Orders[Delivery Mins]), Orders[DQ] = "OK"). - Compare with a plain average.
Show the result
Your result should look like this (fictional sample):
| Measure | Value |
|---|---|
| Average all 6 | 45.8 min |
| Average valid (4 rows) | 10.0 min |
| Rows flagged | 2 (BLK-504, BLK-505) |
Explain what happened
(8 + 12 + 9 + 11) ÷ 4 = 10.0. The two impossible values pulled the plain average up to 275 ÷ 6 = 45.8.
Explore further
- Keep flagged rows for investigation instead of deleting them.
Detailed reference for this lesson:
Some values are technically valid numbers but make no business sense.
Before
| Order ID | Qty | Delivery Time Mins | Status |
|---|---|---|---|
| BLK-70001 | 2 | 9 | Delivered |
| BLK-70002 | -1 | 12 | Delivered |
| BLK-70003 | 1 | 0 | Delivered |
| BLK-70004 | 3 | 500 | Delivered |
| BLK-70005 | 1 | null | Cancelled |
After (flagged)
| Order ID | Data Quality |
|---|---|
| BLK-70001 | OK |
| BLK-70002 | Invalid quantity |
| BLK-70003 | Invalid delivery time |
| BLK-70004 | Delivery time too long |
| BLK-70005 | OK (cancelled) |
First agree the business rules with the operations team (Rani). Our practice rules: Quantity must be more than 0; for delivered orders, delivery time must be between 1 and 180 minutes.
Steps in Power BI
- Add Column › Conditional Column › New column name:
Data Quality. - If Quantity is less than or equal to
0→ OutputInvalid quantity. - Add Clause: Else If Status equals
Cancelled→OK (cancelled). - Add Clause: Else If Delivery Time Mins is less than or equal to
0→Invalid delivery time. - Add Clause: Else If Delivery Time Mins is greater than
180→Delivery time too long. - Else
OK› OK. - Then either filter (Data Quality filter arrow › keep only OK…), or keep all rows and use the flag in a data-quality report page. To find unusual values quickly, use the Column profile (min/max) or Number Filters › Greater Than….
Flag = Table.AddColumn(Source, "Data Quality", each
if [Quantity] <= 0 then "Invalid quantity"
else if [Status] = "Cancelled" then "OK (cancelled)"
else if [Delivery Time Mins] = null or [Delivery Time Mins] <= 0 then "Invalid delivery time"
else if [Delivery Time Mins] > 180 then "Delivery time too long"
else "OK", type text)
Flag, do not silently delete
If you delete invalid rows, totals no longer match the source system, and nobody can explain why. Keep a flag column, show the count of issues on a data-quality page, and exclude flagged rows in measures or with a filter.
Practice task
Add a rule to the conditional column: an order with Amount = 0 and Status = "Delivered" should be flagged "Zero amount". Count the flagged rows per city using Group By (7.23).
Ravindra Bagale's Tip
A common mistake is deleting outliers automatically. A 90-minute delivery might be a real incident that managers need to see. Flag outliers in a column (Normal/Outlier) instead of deleting them, and agree the thresholds with the business. Keep this in mind!
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे outliers आपोआप delete करणं. 90 मिनिटांची delivery ही managers नी बघायला हवी अशी खरी घटना असू शकते. Outliers delete करण्याऐवजी एका column मध्ये flag करा (Normal/Outlier), आणि thresholds business सोबत ठरवा. लक्षात ठेवा!
Ravindra Bagale's Tip – हिंदी
एक common गलती है outliers को अपने-आप delete कर देना. 90 मिनट की delivery कोई असली घटना हो सकती है जिसे managers को देखना चाहिए. Outliers को delete करने की बजाय एक column में flag करो (Normal/Outlier), और thresholds business के साथ मिलकर तय करो. ध्यान रखना!
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Average 45.8 | Outliers included | Exclude DQ ≠ OK |
| Real long deliveries hidden | Range too narrow | Agree the range with operations |
| Rule order wrong | Conditional rules run top to bottom | Put the strict rules first |
Practise independently
Practice task
If the valid range were 1–300, what would the valid average be?
Hints
- Only −5 excluded.
Solution
(8 + 12 + 9 + 240 + 11) ÷ 5 = 56.0 min – which shows why the range must be agreed with the business.
Think like a professional
Never decide outlier rules alone; document the agreed range.
Revision card