cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bpflueger
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

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

View solution in original post

8 REPLIES 8
v-yulgu-msft
Microsoft
Microsoft

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.

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!

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?

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?

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

View solution in original post

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

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.
Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors