Why data transformation ensures quality in Power BI - Formula Trace Breakdown

Sample Data

Sample sales order data with some missing and incorrect values in Amount column.

Cell	Value
A1	OrderID
B1	Customer
C1	OrderDate
D1	Amount
A2	1001
B2	Alice
C2	2024-01-10
D2	250
A3	1002
B3	Bob
C3	2024-01-15
D3	300
A4	1003
B4	Charlie
C4	2024-01-20
D4
A5	1004
B5	Alice
C5	2024-01-25
D5	-50

Formula Trace

CleanAmount = IF(ISNUMBER([Amount]) && [Amount] > 0, [Amount], BLANK())

Step 1: ISNUMBER([Amount]) for each row

Step 2: [Amount] > 0 for each row

Step 3: ISNUMBER([Amount]) && [Amount] > 0 for each row

Step 4: IF(condition, [Amount], BLANK()) for each row

Cell Reference Map

     A       B          C          D
1 | OrderID | Customer | OrderDate | Amount
2 | 1001    | Alice    | 2024-01-10| 250
3 | 1002    | Bob      | 2024-01-15| 300
4 | 1003    | Charlie  | 2024-01-20| null
5 | 1004    | Alice    | 2024-01-25| -50

Formula references column D (Amount) for each row.

The formula uses the Amount column (D2:D5) to check and clean values.

Result

     A       B          C          D        E
1 | OrderID | Customer | OrderDate | Amount | CleanAmount
2 | 1001    | Alice    | 2024-01-10| 250    | 250
3 | 1002    | Bob      | 2024-01-15| 300    | 300
4 | 1003    | Charlie  | 2024-01-20| null   | 
5 | 1004    | Alice    | 2024-01-25| -50    | 

CleanAmount column shows only valid positive amounts, blanks otherwise.

The CleanAmount column ensures data quality by removing invalid or missing values.

Sheet Trace Quiz - 3 Questions

What does the ISNUMBER([Amount]) check do in the formula?

A Replaces Amount with zero

B Checks if Amount is greater than zero

C Checks if Amount is a number

D Calculates total Amount

Key Result

IF(ISNUMBER([Column]) && [Column] > 0, [Column], BLANK()) cleans data by keeping only valid positive numbers.