Filtering Errors in PowerBI

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. 

First Excel

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

Extracting string

defining the delimiter

middle result

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

 

changing 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.

table with errors

error message

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

Editing M

Initial 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

 

final code

 

The final result, without the errors:

Final Result filtering errors in powerbi

  • 1252 views

  • Rate
    [Total: 1    Average: 4/5]
  • Leonid Koyfman

    You can do it without Advanced Editor. E.g. Select column and then click Home->Remove Rows->Remove Errors or right click column header and select Remove Errors.

    • Dennes Torres

      Indeed, my bad. I Just tried and the result is exactly the same as I explained. Probably the references I found were outdated. The oposite instruction can also be done with “Keep Rows” in the toolbar, instead of remove.