Filtering Errors in PowerBI

Comments 2

Share to social media

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

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com

Dennes's contributions