Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Text values are automatically and incorrectly reformated when loaded into model

Hi,

 

I have a data quality analysis report on a payroll system. Data will be exported to a new HRIS soon so there is a renewed focus on improving data quality. One check is looking at text values and identifying values that are no compliant with the data specification. In Power Query Editor it is working correctly; however, once loaded into the model and visualised, some (not all!) values are being reformatted, e.g., from upper case "MR" to title case "Mr", same with "MS" to "Ms". 

 

Payroll data so I can't post that, but here is the M for the line to identify invalid values: 

 

#"Added Invalid Title" = Table.AddColumn(#"Removed Other Columns", "Invalid Title", each 
        if Text.Length( Text.Trim( [Title] ) ) = 0 then false else 
        if List.Contains( { "Mr", "Mrs", "Ms", "Miss", "Dr" }, Text.Trim( [Title] )) then false else true, type logical),

 

 

No transformations are performed on the [Title] column.

 

The values are correctly read from an SQL database. Here is how one row reporting 'true'  is shown in PQ Editor:

 

 

Here is the same row, without any formatting or manipulation, in a table visualisation in Report Editor:

 

 

Why is this happening, and how can I prevent it? ğŸ˜–

6 Replies