cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bpflueger Frequent Visitor
Frequent Visitor

LEFT function is changing case of the last letter

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.)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
bpflueger Frequent Visitor
Frequent Visitor

Re: LEFT function is changing case of the last letter

@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.

8 REPLIES 8
Super User
Super User

Re: LEFT function is changing case of the last letter

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.

Community Support Team
Community Support Team

Re: LEFT function is changing case of the last letter

Hi @bpflueger,

 

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

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bpflueger Frequent Visitor
Frequent Visitor

Re: LEFT function is changing case of the last letter

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:

Before/After Split

 

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 Frequent Visitor
Frequent Visitor

Re: LEFT function is changing case of the last letter

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?

Community Support Team
Community Support Team

Re: LEFT function is changing case of the last letter

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Erikvbueren Frequent Visitor
Frequent Visitor

Re: LEFT function is changing case of the last letter

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?

Highlighted
bpflueger Frequent Visitor
Frequent Visitor

Re: LEFT function is changing case of the last letter

@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 Frequent Visitor
Frequent Visitor

Re: LEFT function is changing case of the last letter

@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!