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. 

PBIError1 Filtering Errors in PowerBI

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

PBIError2 Filtering Errors in PowerBI

PBIError25 Filtering Errors in PowerBI

PBIError35 Filtering Errors in PowerBI

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

PBIError3 Filtering Errors in PowerBI

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.

PBIError4 Filtering Errors in PowerBI

PBIError5 Filtering Errors in PowerBI

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

PBIError6 Filtering Errors in PowerBI

PBIError65 Filtering Errors in PowerBI

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

PBIError7 Filtering Errors in PowerBI

The final result, without the errors:

PBIError8 Filtering Errors in PowerBI

Let’s block ads! (Why?)

SQL – Simple Talk