Power Query converts case during import when it is not asked to do so

[jct999]

Hello

I have an Excel file on a Sharepoint that contains sentences, and I import these data in a PBi dashboard.

Problem : During import, it converts letters between uppercase and lowercase, when it is not asked to do so. And it breaks my data processing logic... : I need to know what is the original case of the sentences.

It seems that for similar sentences, it takes the case of the first sentence then convert the next ones with this case.
This is stupid behavior because it means that the order of the sentences in the source file impacts the way PowerQyery are going to be modified ! Most of the time we do not master this order... 😱

How can I avoid this behaviour ?

Thanks

[Vinnie]

Hi all, is this something that has been fixed by Microsoft at this moment? Or is there a workaround?

I understand that Power BI is not case sensitive and uses the first "version" it encounters for the rest of the rows. Is it possible to somehow force the order of the data load? Lets say that I only have new rows coming in when I order it by ID. By ordering by ID I can somehow steer which version of letters/phrases it uses?

[QC]

Having the same issue, I can't believe MS didn't take it into consideration. It is logic breaking especially considering a lot of hyperlinks generated by various systems are case-sensitive. This is definitely a design flaw.

[CheenuSing]

Power BI doesn't handle case sensitivity that well after the query stage, at the moment.

The Power Query Editor which is case sensitive – which means that it sees “A” and “a” as different characters.

When this data gets loaded into a Power BI dataset, however, you’ll see the “A”.

Because the main Power BI engine is case insensitive – so for example “a” and “A” are considered as the same character and depending on which is first found “A” or “a” all the remaining rows will have same case as first value.

Indications are that Microsoft may not change this behaviour by the Power BI engine.

Please go through the following links for more detailed explanations.

Chris Webb's BI Blog: Power BI And Case Sensitivity (crossjoin.co.uk)

Letter case-sensitivity in DAX, Power BI and Analysis Services - SQLBI

Check out if the links provide solution to your issue.

Cheers

CheenuSing

[v-jingzhang]

Hi @jct999 

I'm afraid this behavior cannot be avoided currently. In Power BI Desktop, strings are case insensitive. It will take the case of the first sentence then convert the next similar sentences with this case, just like that you have found. This is by design. I think its purpose is to reduce the storage size of column data in the model. 

Do you must have upper and lower cases of similar sentences at the same time? If not, you can use Power Query Editor to convert the column to upper case or lower case in advance, then load the data into Power BI Desktop. In this way, sentences in the column will have consistent case. 

Open Power Query Editor through Home > Transform data, then transform the COUNTRY column to lowercase or uppercase. After that, click Close & Apply under Home tab to reload data into Power BI Desktop. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ShirinArshadnia]

hi@jct999 

are you sure that you donot have a Step to change the lower case or uppercase?

check right Pane "Query settings" in power query editor mode and remove step "Uppercased Text" or go to the advanced editor aand find and remove a row like

    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Contract ID", Text.Upper, type text}})

this might be helpful too:

https://xlncad.com/change-letter-case-using-power-query/