Replace Error values in Power BI data model

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.