Text values are automatically and incorrectly reformated when loaded into model

[Anonymous]

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? 😖

[Anonymous]

Hi @Anonymous ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[WanderingBI]

Wow! That is indeed crazy. Seems like there is also no easy workaround. Even if you add an "_" in front of each value you get the same issue.

What I do not understand is why this strange result is not also shown after running the Query in the Power Query GUI.

[Ahmedx · Accepted solution]

refer to the following
1) The capitalization (uppercase / lowercase) of my text data changed in Power BI! Why?

https://www.dutchdatadude.com/some-of-you-might-have-seen-this-before-you-load-some-data-into-power-...

2)

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

3)

https://xxlbi.com/blog/power-bi-antipatterns-12/

[Anonymous]

I guess I will have to duplicate the invalid values in a new column in PQ so they are not corrected to the valid values. Ironically, increasing the data model size because of a feature intended to decrease the data model size. 🙃

[Anonymous]

Thanks. At least I know I'm not going (as) crazy.

Just found this post on the same subject: Power Query converts case during import when it is... - Microsoft Fabric Community

[WanderingBI]

Hi!

I can reproduce your issue:

In Power Query:

Table view after loading data model:

It propably has to do with the "Formatting" of the text type value contents: