Forum Discussion
Power BI automatically normalizing text casing during data load
Hello everyone,
I am building a Power BI connector that loads data from a server and have observed an unexpected behavior with text values.
For example, the source data contains the following values in the City column:
- PunE
- Pune
- pune
- PUNE
However, after loading the data into Power BI, all values are displayed as PunE, matching the casing of the first occurrence.
It appears that Power BI treats text values as case-insensitive and preserves the casing of the first occurrence of a particular text value.
My questions are:
- Is this the expected behavior of Power BI during data loading/modeling?
- If this is expected behavior, is Microsoft/Power BI working on any changes or improvements to preserve the original casing of text values?
- Is there any way to preserve the exact casing of each source value?
- Since I am developing a connector that retrieves data directly from a server, are there any connector-side measures or transformations I can apply to prevent this behavior?
I have attached screenshots illustrating the source data and the resulting Power BI output.
Excel Table -
| Name | Age | City |
| Bhargav | 22 | PunE |
| Dhairya | 22 | Pune |
| Abhishek | 22 | pune |
| Gautam | 22 | PUNE |
Power BI Table which is loaded from above excel file -
| Name | Age | City |
| Bhargav | 22 | PunE |
| Dhairya | 22 | PunE |
| Abhishek | 22 | PunE |
| Gautam | 22 | PunE |
Any guidance on how this can be handled at the connector level would be appreciated.
Hi KumbharM,
Thanks for reaching out to the Microsoft Fabric Community forum.Yes, the behavior described is documented for Power BI.
The Power BI engine that stores and queries data is case insensitive, so it treats text values that differ only by capitalization as the same value. In contrast, Power Query is case sensitive, so it can display the casing as stored in the source before the data is loaded into the Power BI model.Microsoft also explains that, when data is loaded, the Power BI engine evaluates rows from top to bottom and maintains a dictionary of unique text values. When it encounters values that differ only by case, they are treated as the same value and the existing variation is referenced. Therefore, the capitalization displayed in the model can correspond to the first variation encountered during loading.
This explains the behavior in the example where PunE, Pune, pune, and PUNE are loaded and subsequently displayed using the casing of the first occurrence.
For a connector scenario, once the values are loaded into the Power BI engine, case-only variations are treated as the same value. The Microsoft documentation specifically recommends, for DirectQuery with a case-sensitive data source, normalizing casing in the source query or in Power Query Editor.
For more details, please refer to the below Official Microsoft Documentation:
Data types in Power BI - Power BI | Microsoft Learn
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft LearnI hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
6 Replies
v-abhinavmuCommunity Support
Hi KumbharM,
Thanks for reaching out to the Microsoft Fabric Community forum.Yes, the behavior described is documented for Power BI.
The Power BI engine that stores and queries data is case insensitive, so it treats text values that differ only by capitalization as the same value. In contrast, Power Query is case sensitive, so it can display the casing as stored in the source before the data is loaded into the Power BI model.Microsoft also explains that, when data is loaded, the Power BI engine evaluates rows from top to bottom and maintains a dictionary of unique text values. When it encounters values that differ only by case, they are treated as the same value and the existing variation is referenced. Therefore, the capitalization displayed in the model can correspond to the first variation encountered during loading.
This explains the behavior in the example where PunE, Pune, pune, and PUNE are loaded and subsequently displayed using the casing of the first occurrence.
For a connector scenario, once the values are loaded into the Power BI engine, case-only variations are treated as the same value. The Microsoft documentation specifically recommends, for DirectQuery with a case-sensitive data source, normalizing casing in the source query or in Power Query Editor.
For more details, please refer to the below Official Microsoft Documentation:
Data types in Power BI - Power BI | Microsoft Learn
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft LearnI hope this helps. Please feel free to reach out if you have any further questions.
Thank you.- tharunkumarRTK
Super User
Hello KumbharM
Yes this is an expected behavior.
To solve this problem I think there are few possible workarounds
- You can add additional characters, Chris webb wrote a blogs about the same
https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ - You can change the collation in model.tmdl file, please refer the below video
https://www.youtube.com/watch?v=55Jekgmyai4
read my blogs here: https://www.techietips.co.in/
- You can add additional characters, Chris webb wrote a blogs about the same
- ShivekMaharaj
Community Champion
Hi KumbharM,
v-abhinavmu's explanation is correct. I would add one important qualification around the workaround.
Microsoft's current Power BI data type documentation explicitly states that Power Query is case-sensitive, while the Power BI engine that stores and queries the data is case-insensitive.
That means values such as:
PunE Pune pune PUNEcan remain distinct in Power Query, but once loaded into the semantic model the engine can treat them as the same text value and retain one casing as the canonical representation.
So from a connector perspective, I would not expect there to be a flag that disables this behaviour after the data reaches the Power BI engine.
I would also be careful with DirectQuery as a workaround.
Microsoft specifically warns that when DirectQuery is used against a case-sensitive source, values that differ only by case can produce undefined results because the source and Power BI disagree about whether those values are distinct.
Microsoft's documented recommendation in that situation is to normalize the casing in the source query or in Power Query before the values reach the model.
So if City is logically case-insensitive, I would standardize it upstream, for example to one agreed representation, rather than rely on whichever casing is encountered first during model loading.
If the different casing is actually meaningful business data and PunE, Pune and PUNE must remain separate values, then I would treat that as a modelling requirement rather than only a connector-formatting issue. The standard Power BI semantic model behaviour does not provide a reliable way to preserve case-only distinctions in one text column.
I would also avoid adding invisible or extra characters simply to force the values to remain distinct unless there is a very specific reason, because that changes the actual values and can affect relationships, filtering and comparisons.
So for a custom connector, the safest design is normally:
- preserve the source values in Power Query
- decide explicitly whether casing has business meaning
- if casing does not matter, normalize it before model load
- if casing does matter, redesign the key/model so uniqueness does not depend only on letter casing
Microsoft also covers the same DirectQuery caution in the DirectQuery guidance.AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- Praful_Potphode
Hi KumbharM
Please refer answers by ShivekMaharaj and tharunkumarRTK
if its still not clear let us know.
Regards,
Praful
- v-abhinavmu
Hi KumbharM,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you. - v-abhinavmu
Hi KumbharM,
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
