Power Query Proper Name Check

Use Power Query to check if text fields are Proper, Capital, Lower & more


I come up with a situation where I need to check some text fields like ‘Name’ regarding the Quality of data entry in a PowerBI data model. Specific I needed to see if the Name was in Proper writing meaning each word/name must start with a capital letter folowing by lower ones. An easy job for excel formulas but for PowerPivot?

My initial thought to create a calculated column in PowerPivot ( didn't tried it ) which will indicate if the specific text field will be correct (Proper) or not. Really fast I realized that PowerPivot has limited text formulas or at least not as many as Power Query so I tried the formula in PowerQuery. Which turned out to be incredibly easier than I believed.

Let’s started, this is our table with only 2 columns with ‘id’ and ‘Name’

We load it to PowerQuery, and add a custom column

We add the field name ‘Name Proper Check’ then write a simple if M formula

=

if Text.Proper([Name]) = [Name] then “Proper” else “Needs Check”

Due to PowerQuery language being case sensitive ( powerpivot is not ) the Text.Proper([Name]) results in the actual name converted to Proper and compares it with the actual [Name] returning FALSE when both are not the same….What we wanted actually.

We can load it to the data model and then create a pivot table that can count the number of not proper values and also drill down to the specific id or Name..

This is a useful scenario when you can check the consistency of text fields like Names, emails etc.