topic Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries in Data Engineering

How to deal with case sensitivity in Dataverse tables without using lower function in queries

antoec — Thu, 13 Feb 2025 12:31:37 GMT

In dataverse build by dynamics365 FNO table2 is child of table1 id field in table1 is uppercase but id field in table2 is lowercase. in query when linking both tables using id field there won't be any data due to case sentivity.

using lower function in everyquery will be expensive. Is there any configuration or setting which could be changed to deal with this permanently.

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

nilendraFabric — Thu, 13 Feb 2025 14:21:49 GMT

Hello @antoec

In Dataverse, the collation settings—including whether the data comparisons are case-sensitive or case-insensitive—are defined when the environment is created and cannot be changed afterward. This means that if your environment was configured with a case-sensitive collation, you cannot later switch it to be case-insensitive through a configuration change. Therefore, to resolve issues where related fields have mismatched cases, the only practical solutions are to normalize the data—for example, by ensuring that all key values are stored in the same case—or to use computed columns that store a pre-normalized version of the data. This approach typically avoids the performance overhead of using functions like Lower() or Upper() in every query.

there isn’t a built‐in configuration setting in Dataverse to globally disable case sensitivity for text comparisons. Instead, you must ensure that the data used for linking tables is normalized so that both sides of the join use the same case.

If this is helpful please accept the answer

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

antoec — Fri, 14 Feb 2025 04:41:35 GMT

@nilendraFabric thank you for the reply. Could guide any documentations or reference where i could follow and create a dataverse which is case-insensitive.

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

v-csrikanth — Fri, 14 Feb 2025 08:25:55 GMT

Hi @antoec
I sincerely appreciate @nilendraFabric for taking the time to provide a thoughtful and insightful solution.
Note: For reference regarding case sensitivity, please refer to the following document.
https://learn.microsoft.com/en-us/power-platform/admin/language-collations

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

nilendraFabric — Fri, 14 Feb 2025 08:49:11 GMT

https://learn.microsoft.com/en-us/power-platform/admin/language-collations

key points in the doc

Default Collation Settings:
• When creating a Dataverse environment, administrators select a default language. This choice determines the collation applied to the SQL database that underpins the Dataverse instance.
• Collation settings affect sorting, filtering, and text comparisons, including case sensitivity and accent sensitivity.

All languages in Dataverse use case-insensitive collations by default, denoted by `_CI`. For example, “Cafe” and “cafe” are treated as the same word

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

v-csrikanth — Thu, 20 Feb 2025 11:32:13 GMT

Hi @antoec
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you,
Cheri Srikanth

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

v-csrikanth — Mon, 24 Feb 2025 13:05:37 GMT

Hi @antoec
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Thanks
Cheri Srikanth

Re: How to deal with case sensitivity in Dataverse tables without using lower function in queries

v-csrikanth — Thu, 27 Feb 2025 06:47:57 GMT