Forum Discussion
case sensitive data in Direct Query mode
You have a data source that contains a column. The column contains case sensitive data. You have a Power BI semantic model in DirectQuery mode. You connect to the model and discover that it contains unde ned values and errors. You need to resolve the issue. Solution: You add an index key and normalize casing in the data source.
Does this work? Ask for discussion and confirmation.
I would like to yes but not sure, according to the document: (but it says source query or power query editor not data source)
Because the engine that stores and queries data in Power BI is case insensitive, take special care when you work in DirectQuery mode with a case-sensitive source. Power BI assumes that the source has eliminated duplicate rows. Because Power BI is case insensitive, it treats two values that differ only by case as duplicate, whereas the source might not treat them as such. In such cases, the final result is undefined.
To avoid this situation, if you use DirectQuery mode with a case-sensitive data source, normalize casing in the source query or in Power Query Editor.
Yes, normalizing the casing in Power Query or at the data source level is the recommended approach to resolve the case-sensitivity issue in DirectQuery mode. Adding an index key can help distinguish rows if needed, but normalizing should be your first step to avoid undefined results.
5 Replies
Hi,
The best option is to make the case consistent in the backend itself. If not possible, then Power Query. Last thing you can do is in DAX, you may use LOWER or UPPER functions to make all values appear in the same case.
Hope this resolves your problem, if it does, then please mark it as solution provided, Thanks!
Hi samratpbi ,
Thanks for your reply. So based on the solution "add an index key and normalize casing in the data source" should not work?
Yes, normalizing the casing in Power Query or at the data source level is the recommended approach to resolve the case-sensitivity issue in DirectQuery mode. Adding an index key can help distinguish rows if needed, but normalizing should be your first step to avoid undefined results.
Thank you Kedar_Pande for the reply. Confirmed this Solution: You add an index key and normalize casing in the data source will work for direct query mode.
Hi New_worker , hello Kedar_Pande and samratpbi, thank you for your prompt reply!
Thank you for discussing the issue and confirming the solution.
Please remember to accept your solution as answer.
This will greatly assist others who encounter similar questions in the forum.
Thanks again for your valuable contribution.
