Data Quality Checker - Scan entire row (all columns) for values like 'na', 'n/a', 'tbd', 'TBD'

[RichardJ]

Hi,

I have a database with many columns some of which have spurious values 'na', 'n/a', 'tbd'.

Unfortunately the entries can't be validated and caught at the time of entry.

To find and resolve these entries my current plan is to concatate all columns into a single string with a delimiter e.g.

|Orange|123.23|Banana||12|Apple|na|Grape|n/a|Peach|tbd|

Then, a conditional column would be used to search for the relevant string |n/a|, |na|, |tbd|

Although this would work it seems innefficient, especially since it would require ongoing maintenance as further columns were added.

Can anyone advise on a better approach to find the rows where the incorrect entries have occurred?

Thanks,

Richard

[RichardJ]

Hi @DataZoe,

Thanks for the reply. I should have been clearer in my original question.

I don't wish to clean up the data in Power BI but would prefer if the users of the original database cleaned up their entries with useful, meaningful data rather than the n/a, na, tbd generic statements.

I was hoping to find a way to scan all columns in the table at once for those values and identify which rows/records should be visible to the users/Data Quality Manager in a Power BI Dashboard so that the records could be fixed by the people who didnt enter the correct info originally.

Hope that makes sense.

Thanks,
Richard

[DataZoe · Accepted solution]

@RichardJ Oh, thank you for clarifying, that does make sense now. For that, I would then suggest you unpivot your data in Power Query, so you have all your field names in an Attribute column and all your data in a Value column. Then you can do your counts on Value and show by column in a report.  

[RichardJ]

Very nice solution @DataZoe . This is far more efficient than what I was thinking. Fabulous.

[DataZoe]

@RichardJ In the transform data of the power bi file you can select multiple columns (or all of them) in a table and then replace values, changing them to null or another value of your choosing.