Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

7.16 Outliers and Invalid Values

By Last updated:

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

  1. Add Column › Conditional Column › New column name: Data Quality.
  2. If Quantity is less than or equal to 0 → Output Invalid quantity.
  3. Add Clause: Else If Status equals Cancelled → OK (cancelled).
  4. Add Clause: Else If Delivery Time Mins is less than or equal to 0 → Invalid delivery time.
  5. Add Clause: Else If Delivery Time Mins is greater than 180 → Delivery time too long.
  6. Else OK › OK.
  7. 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!