Here is the Sample Excel data source I’ve created for this Example. Here we will replace the error values in our Query Editor. MyCol3 is Sum of the first 2 columns. Once imported in the data model in Power BI, the 2 rows with values a and b in MyCol2 will show Error in MyCol3.
Import the Excel as below:
Get Data->Excel->Select the Excel file from which you want to import->Select the Sheet you created with the Preview.
Importing the sheet shows the following dialog box:
The data model is shown as below in Power BI:
Right-click on MyCol3 and select Edit Query. This will open the Query Editor.
In the Query Editor, right-click on MyCol3 and Select Replace Errors:
Same option is available in Replace Values drop-down menu under Home Tab in the Ribbon.
Enter the required value to replace with Error and click OK:
The Final values in the data model will be visible as below:
The Applied Steps in the Query Editor will be shown as below for Sheet1 data model:
Click on Close & Apply in the Home Tab to save your changes.
Similar steps can be used to Replace Error values in Excel using Power Query.