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.

@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
Super User

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors