Upper Case Changed to Lower Case in Data Set but Power BI does not Change

[mcartoon]

This issue is present in both Desktop and Service.

Scenario: Title field changes embedded City/State from Upper Case to Lower Case. No other changes.

In desktop, the change is reflected when data set is updated. It looks fine at the start of transforms and at the end. However, when I close and apply and then refresh, the entry in the Data Table in Power BI Desktop still has the City/State in Upper Case.

Cleared Cache under File Options. Retarted Desktop. No Change.

If I simply add a space to the title after change to Lower Case, it gets updated properly.

Seems like there is an internal cache not being cleared which does not see a mere change in case as a change in the data.

Anyone else experience this?

[SamInogic]

As per our understanding, you've encountered an issue with Power BI where case changes in the source data are not reflected in the data model after refreshing. This behaviour is not a bug; it is by design. 

Power Query is case-sensitive, while the main Power BI engine is case-sensitive. For example, "As+", "aS+", "AS+", and "as+" are considered the same value in Power BI. If you have these variations in your data source and in Power Query, during data import into the Power BI dataset, they will all be replaced by one of them. Typically, it will be the first one loaded into the dataset. If "as+" is the first loaded into the dataset, then all subsequent "AS+", "As+", and "aS+" records will be replaced with "as+". However, it doesn't matter what exact case you see in Power BI ("as+", "AS+", "As+", or "aS+"). For the data model (relationships) and DAX comparison operators (e.g., "="), they are not different. While there are case-sensitive functions like EXACT(), you can't have values that are different only by case in one column.

Prefer the Blog: This https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ explains the problem in detail.

Thanks!

[johnguilbert]

Hi.

Yes I implemented those Power Query steps in Transform Data of Power BI Desktop and works....amazingly. You say this works as designed. I can't believe this. This means anyone using Paginated Reports displaying data from the Semantic Model will have case changes. I don't see this as by design. I see this as a carry over issue from bringing SSRS into Power BI. If this was by design you wouldn't need these ridiculous steps to persist case - you'd have a toggle or one command. This needs fixing and is a bug carried over from the port. This is definately not what you would want to happen by design for Paginated Reports.

[johnguilbert]

Just to add. The example shown gives adding special characters on end of Lowercase characters. If we only have a few upper case characters will it still work if I added the special case characters to Uppercase characters? Haven't tried this. ** Be warned ** This will break your wrapping of text in a Power BI Report Builder field. The fix is to remove the special characters in the DAX or as I did on the field: 

Replace(Fields!YourFieldName.Value, ChrW(8203), "")

This retains case and fixes the wrap issue as problem was getting the correct case into Report Builder initially. Once it's in you can remove these characters. Very messy and something Microsoft SHOULD be fixing.

[SaiTejaTalasila]

Hi @mcartoon ,

Main Power BI engine is case insensitive – so for example “a” and “A”.So, you can use either upper-case or lower-case transformation on those columns to make it stable.

You can refer this -

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

Thanks,

Sai Teja 

[johnguilbert]

Problem is the NOTE field is single line of text in Dataverse - max 1000 characters . It is only after the DAX query in Power BI Report Builder that the specific text in a record is changed to lower case although upper case in Power BI Semantic Model.   

[johnguilbert]

Yes. I have a Power BI Semantic model that picks data up from dataverse with a number of datasets. One sepecfic dataset is what I am having issues with. This Semantic model with this dataset is used in a Paginated Report using Power BI Report Builder. There is one record where the text has been changed to lower case from upper case - "Golden Boy" is shown as "golden boy".  I, for the life of me, couldn't see the issue anywhere and pressume this is an internal cache issue as you say. Caching is switched off on the Semantic Model. Using Power BI Desktop it shows the text correctly as "Golden Boy" in the dataset. The issue is the Model dataset in Power BI Report Builder. Exactly the same entries except "golden boy" is shown. I am assuming this is either a dodgey DAX Query for the Dataset in Power BI Report Builder or more likely some caching used by DAX.

[Greg_Deckler]

@mcartoon Hard to be certain but perhaps you are running into Power BI's joyful "pre-emptive casing" behavior?

[Ghhousuddin]

It seems like you've encountered an issue with Power BI where case changes in the source data are not reflected in the data model after refreshing. There could be various reasons for this behavior, such as an internal cache not being updated or a case-insensitive comparison setting. Here are some suggestions to force Power BI to recognize the case changes:

1. Add an index column in Power Query:

   In the Power Query Editor, go to the "Add Column" tab and click on "Index Column". This will create a new index column with unique integer values for each row. This additional column should help Power BI recognize the case changes when you refresh your data.

2. Create a custom column for lowercase conversion:

   In the Power Query Editor, create a custom column that converts the Title field into lowercase using the Text.Lower function. This will ensure that the case change is applied in the data model.

3. Text.Lower([Title])

4. Replace the original Title field with this custom column in your data model and visuals.

5. Clear the data cache and refresh your data:

   • Go to "File" > "Options and settings" > "Options" > "Data Load" in Power BI Desktop.
   • Click on "Clear Cache" to remove any cached data.
   • Close and restart Power BI Desktop.
   • Refresh your data and check if the case changes are now reflected in the Data Table.