LEFT function is changing case of the last letter

[bpflueger]

I have a very simple column setup in PowerBI using the LEFT function.  The column is defined as MyColumn = LEFT(MyData[ID],15).  I expect the result to be case-sensitive with respect to the original value, but this is not happening sometimes.

On seemingly random rows, the result from LEFT is changing the case of the last letter in the result.  For example, if the original ID was 1234567890000AbCdE, LEFT(col,15) is returning 1234567890000AB.  I've also seen it go in the opposite direction, changing an uppercase to lowercase.  This doesn't happen on all rows, only some.

When pulling in my full dataset, I end up with duplicate short IDs due to the case changes and cannot link the tables as required.  What's causing this?

(As you may have guessed, this involves Salesforce data.  I have an external dataset that only knows the "short" IDs and am trying to link it up with the actual SFDC object data.  There's no way to get the full 18 character IDs into the external data, so I need to rely on case-sensitive 15 character IDs.)

[v-yulgu-msft]

Hi @bpflueger,

I cannot reproduce your problem. Actually, the result returned by LEFT is case-sensitive.

You can try Ashish_Mathur's suggestion that split column in Query Editor to see whether characters are case-sensitive. Also, try to re-create data source connection and re-load data model to check if it works. 

Best regards,
Yuliana Gu

[bpflueger]

Very odd, I'm having the same exact issue with split via Query Editor.

Here are four example IDs that I looked at.  Before the split is on the left, after the split is on the right.  Otherwise these are the same exact rows/data:

Basically the same behavior I see with LEFT.

As mentioned in the original post, it's not always lowercase to uppercase - sometimes the opposite is true too.  I have no idea what could be causing this - I feel like I'm going a bit crazy!

[bpflueger]

I've experimented a bit more, and wanted to add a little more info to my post.  Here are three views of the same set of IDs:

"Original Data" is a direct import of some sample IDs from an Excel sheet.  I imported this into a brand new PowerBI project, nothing else inside.

"Split Column" is what I see inside Query Editor when I split the column at 15 chars.  This is what I see before I hit "Close & Apply".  This is right.  The results are case-sensitive and unique as expected.

"Final Split" is what I see in the table after I hit "Close & Apply".  This is wrong.  There are now several non-unique IDs as a result of the uppercase/lowercase change.

I'm using the latest release of PowerBI Desktop (Sept 2017), if it matters.  I had a colleague test with the same dataset and he ended up with the same results.  Is this a bug?

[Erikvbueren]

Hello,

I have exact the same issue, even without using a LEFT function. We have a case sensitive Web URL which is adjusted by Power BI. In Query 1 the value is corrupted (due to a single change of case of one character), in a reference of the same query the same value is correct and can be used as Web URL.

Do you have some ideas or more examples?

[bpflueger · Accepted solution]

@Erikvbueren - Per the Product team, this is apparently by design:  http://community.powerbi.com/t5/Issues/LEFT-Function-and-Query-Editor-split-changing-case-of-values/...

I haven't found a solution, and given the response it sounds like this is not considered an issue, so I'm not holding my breath for any change.  I've had to use another BI tool for this set of reports, unfortunately.

[Erikvbueren]

@bpflueger: Thx for the answer. That's odd! We will keep browsing for a solution. We use our text string for a Web link that is case-sensitive, so due to this issue the web link is not valid anymore. 

I will post an update if we've found something!

[v-yulgu-msft]

Hi @bpflueger,

This is very odd. Have you tried to re-load dataset and re-create the report in a new pbix file for a test?

Regards,
Yuliana Gu

[Ashish_Mathur]

Hi,

I am not sure of why that is happening but try to suse the split column feature in Power Query to extract 15 characters from the left.