Nag Arena: How to highlight bad data in Power BI

Most often we run into data errors in Power BI, and very often they are due to datatype mismatches. Instances like text data appearing on numeric columns, numeric data showing up on date columns is a run-of-the-mill. Due to these type mismatches, data is blanked out in Power BI.

Original data:

In Power BI:

This situation can be better handled using Power Query. We can separate out data rows with errors and show them in a different tab. We can also highlight this error data for business to easily identify and fix them. In the next data refresh, if these errors are corrected, they will automatically disappear from this errors tab and appear in main data.

This is how Error tab will look like:

Disclaimer! this method may duplicate data a bit, all the non-text columns must be duplicated to show data that caused errors. As in my experience I can tell, you don't have to watch out for all columns. There will be limited fields you need as dates or in numeric format. I don't say this method is best, but you can use this on limited data, mostly when your data is coming from files as database will have this taken care of already.

So lets see how this can be done.

Notice how text data in non-text columns is resulting in errors. If you don't see these errors, that means data types are not defined yet. You can use "Detect Data Type" option or click on the icons next to each column names manually to set appropriate data type.

Create duplicate columns for these non-text fields and give a suffix "Org" to the column names. Make sure the data type of these duplicate columns is text.

Add an index column to the table in case you don't have any unique keys in the data, this step is optional.

Creating errors table.

Now clone the main table and name it as "Projects_Errors"

On this new table, click on "Keep Errors", make sure you don't select any column before you do this.

Now click on the "Advanced Editor" and open Power Query script.

Change Source to point to main table (in our case Projects), we should do this so in future if any changes are made to Projects table, they will flow into Errors table as well.

Now change the datatypes of all error columns to Text. We do this so we can replace errors with a custom text.

Use "Replace Errors" option to replace all errors with text "#Error". You can do this column by column or use below script to do all columns at once.

After you replace errors, your data will look like this.

Here is the full script

let
Source = Projects,
#"Kept Errors" = Table.SelectRowsWithErrors(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Kept Errors",{{"Target Start", type text}, {"Target Finish", type text}, {"% Comp", type text}}),
Columns = Table.ColumnNames(#"Changed Type"),
#"Replace Errors" = Table.ReplaceErrorValues(#"Changed Type", List.Transform(Columns, each {_, "#Error"}))
in
#"Replace Errors"

Now lets get to the front end.

Create a new tab in Power BI and add data from newly created errors table. Add original columns for non-text fields. Your new table visual will look like this.