7. Data Cleaning A–Z in Power Query
7.15 Errors: Remove, Replace, Keep and try … otherwise
Cell-level errors appear when a conversion fails, for example "NA" in a number column.
Before (after Change Type)
| Order ID | Delivery Time Mins |
|---|---|
| BLK-60001 | 9 |
| BLK-60002 | Error |
| BLK-60003 | 14 |
| BLK-60004 | Error |
After (Replace Errors with null)
| Order ID | Delivery Time Mins |
|---|---|
| BLK-60001 | 9 |
| BLK-60002 | null |
| BLK-60003 | 14 |
| BLK-60004 | null |
Steps in Power BI
- Investigate first. Duplicate the query (right-click › Duplicate), select the column › Home › Keep Rows › Keep Errors. Click a cell's empty space and read the error message at the bottom (for example DataFormat.Error: We couldn't convert to Number. Details: NA).
- Replace: right-click the column header › Replace Errors… › value
null(or 0 if it makes business sense) › OK. - Remove: select the column › Home › Remove Rows › Remove Errors (use this when the whole row is unusable).
- Prevent in formulas: in a Custom Column, wrap risky logic in
try … otherwise.
Replaced = Table.ReplaceErrorValues(Typed, {{"Delivery Time Mins", null}}),
NoErrors = Table.RemoveRowsWithErrors(Typed, {"Amount"}),
Safe = Table.AddColumn(Source, "Delivery Mins Safe",
each try Number.From([Delivery Time Mins]) otherwise null, type number),
// keep the error message for a data-quality report
WithMsg = Table.AddColumn(Source, "Error Reason",
each let r = try Number.From([Delivery Time Mins]) in
if r[HasError] then r[Error][Message] else null)
| Type of error | Example | Fix |
|---|---|---|
| Cell-level | "NA" converted to number | Replace/Remove Errors, try … otherwise |
| Step-level | DataSource.NotFound (file moved), The column 'Amount' of the table wasn't found | Fix the source path (Data source settings / parameter) or the step that refers to the renamed column |
Errors la ghabru naka – te tumhala sangtat ki data madhe kay chukla aahe. Aadhi bagha, mag handle kara.
Ravindra Bagale's Tip
Friends, many students remove error rows without looking at them. Ten error rows today can be ten thousand next month. Use Keep Errors to see the cause (for example "NA" typed by staff), fix it before the type change by replacing "NA" with null, and only then remove anything that is truly unusable. Never forget this.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students error rows न बघताच काढून टाकतात. आज दहा error rows असतील तर पुढच्या महिन्यात दहा हजार असू शकतात. कारण बघण्यासाठी Keep Errors वापरा (उदाहरणार्थ staff ने type केलेलं "NA"), type change च्या आधी "NA" ला null ने replace करून दुरुस्त करा, आणि त्यानंतरच खरोखर निरुपयोगी असेल ते काढा. हे अजिबात विसरू नका.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students error rows को देखे बिना हटा देते हैं. आज दस error rows हैं तो अगले महीने दस हज़ार हो सकती हैं. वजह देखने के लिए Keep Errors इस्तेमाल करो (जैसे staff का type किया "NA"), type change से पहले "NA" को null से replace करके ठीक करो, और उसके बाद ही वह हटाओ जो सच में किसी काम का नहीं. यह बिल्कुल मत भूलना.
Practice task
Mhanje asa: in a Solapur (Murarji Peth) file, Quantity contains "2", "3 pcs" and "-". Create a Custom Column using try Number.From(Text.Select([Quantity], {"0".."9"})) otherwise null, and list the rows that still return null.