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.