I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive an expression error. Let’s build an example filtering errors in PowerBI.

The image below illustrate a small set of data in excel that will result in this problem. The objective is to extract the numbers and exclude all the rows without numbers. 

After importing the excel file in PowerBI, let’s extract all the content after the “=” sign using Transform => Extract=> Text After Delimiter. 

Now, let’s change the data type to decimal using Transform => Data Type

 

 

We have two errors in this resultset, as you may notice in the images below. There is no way to exclude these errors using the interface, we need to code in M to achieve this. There are two useful functions available to filter errors: Table.RemoveRowsWithErrors and Table.SelectRowsWithErrors, the names already explain what they do.

We need to open the Advanced Editor, inside View, to edit the M code.

 

We will add a new line before the “In” statement. In this line, we will create a new name for our result, like this:

After the “=” sign we can use the function, it has two parameters: The name of the result of the previous line and the column we want to check for errors.

Two more details to go:

• The previous line need an extra comma at the end
• After the “in” statement, we need to change the name of the final result set to our new name

 

 

The final result, without the errors: