Counting errors in Rich Data types

[Joe Pineapples]

Hi - we have a large Excel workbook that we are using to log requests. To help with data integrity we have a control dashboard (separate tab) that identifies any columns with missing or wrong values in the main data set. One of the fields in the data set is Country and we are using the Rich Data type (Geography) to help with the data entry.

Q: Is there a way to count the number of Country entries that need to be investigated and to show this number in a cell on our dashboard. In the example below there are 2 records that are wrong - its easy to eyeball - but in our data set we have over 4000 records so its not easy to spot them.

I have tried using the Dot Formulas & FIELDVALUE Function but there doesn't appear to be a field for error. Or maybe I missed it.




The only way I have found so far is to refresh the Geography Data Type on the Country column. When I do this the selector helper comes up (see pic below) with the entries that need correcting. This is helpful to resolve the problems - but doesn't tell me that there were two problems that needed investigating.

At the moment we have a weekly check that includes this process - but it would be great to have the number of errors appear on our data quality dashboard

Big thanks in advance for any help

Best

Joe

 

[Joe Pineapples]

Lol - funny how writing a problem down makes you think about it in a different way.

If I use a combination of a DOT Formula and a regular error count I can get the answer

Apologies if I have wasted your time !