LEFT Function and Query Editor split changing case of values

[bpflueger]

I already posted this in the Desktop forum, but didn't seem to get any answers.  I'm fairly certain it's a bug so I'm reposting here.

I'm having an issue (Sept 2017 release) where certain values have unexpected case changes after going through LEFT or Query Editor's Split Column function.  The odd part is that the preview of the split column is correct, but the final result (after applying the change) is incorrect.  An example:

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 data is correct - the results are case-sensitive and unique as expected.

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

Help!

[v-haibl-msft]

@bpflueger

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 49076620
I’ll post here once I get any update about it.

Best Regards,
Herbert

[v-haibl-msft]

@bpflueger

I’ve got response from the Product Team.

This is by design. Power BI workbook uses a case insensitive language setting. As a result, strings in the same column which differ only by cases will be consolidated into a single string depending on whichever happens to be imported first. Query Editor UI displays the strings in the original case before the Import, Data View shows the strings after the Import.

Best Regards,
Herbert

[bpflueger]

@v-haibl-msft:  Thanks for your reply.

These IDs are only unique if case-sensitive - the "by design" functionality makes it impossible to build relationships between this data and SFDC.  Does the explanation apply to the LEFT function as well?  I see the same behavior there, so I'm assuming so.

What I'm getting from the response is that there's no way for me to use PowerBI to build these reports.  I'm feeding in unique IDs, PowerBI is converting them to non-unique IDs by design, and there's no way to prevent this...  Is that an accurate statement?