Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AliceW
Impactful Individual
Impactful Individual

URL link changes capital letters to regular ones after loading Power Query. Why?

Hello everyone,

 

I have a report which takes data from Salesforce. Each Opportunity has its own URL like:

https://mycompany.my.salesforce.com/00abC

 

I've encountered one case where two Opportunities have the same string of numbers and letters, with the only exception being that some letters are CAPITALS and some are not.

 Untitled.png

 

Power Query (using Salesforce Objects) brings them correctly:

Untitled2.png

 

Their type is text. So far, so good.

 

Back in the Desktop, after the Power Query loads, I convert this URL field into Data Category: Web URL, to enable the users to click on the link. And here is the problem. Some letters switch to capitals! Not all, but some.

Untitled3.png

This means everyone who clicks on either link gets to the same Opp, which is not correct.

 

What can be done about this?

 

Thank you,

 

Alice

1 ACCEPTED SOLUTION

Hi @AliceW ,

"And I couldn't figure out what you mean by the blank."
I mean insert a space before a1.
Actually for this problem, there are currently two possible solutions:

1. Use M language

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ 

2. Modify value
Just like I did before, insert the corresponding characters in the column value to distinguish the column value.

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

11 REPLIES 11
zelroberic
Frequent Visitor

Hi Alice,

I am also loading a Sales Force URL and I am having a similar issue. It looks look the issue is native to Power BI as DAX/the model is not case sensitive and Power Query is. So the model alters the letters when they are loaded. Not good for a URL!

I doesn't seem to me that a satisfactory solution was given. Did you ever resolve this issue? And if so what did you do?

 

Thanks,

Rob

AliceW
Impactful Individual
Impactful Individual

Hi Rob,

I ended up creating a new column using a basic

CONCATENATE("https://YOURCOMPANY.my.salesforce.com/", [Opportunity  Internal ID])

Salesforce was pretty clever - the internal ID of an opp is also its link.

Hopefully it works for you too.

Alice

Hi Alice.

Ok this makes sense.

I am actually in an Invoice table and it appears that the ID also has letters in it. I will speak to our Sales Force administrator to get more clarity. Thanks for getting back to me so fast. I don’t under why this issue doesn’t come up a lot when pulling information from the web.

 

Thanks Rob

AliceW
Impactful Individual
Impactful Individual

Cool, you have invoicing in Salesforce! I think the logic behind the 'Internal ID' is the same. I know I use it for Leads too.

We import our invoices from NetSuite our ERP system so our Sales people can have access to their Sales and Orders.

Interesting I didn't see the field [Operational Internal ID] at first glance. I will look again. The [ID] field I saw still contained 

letters. The internal ID is totally numeric? 

Thanks for your help so far. I really appreciate it.

Rob

AliceW
Impactful Individual
Impactful Individual

Hi Rob,

The internal ID is (for us) a combination of letters and numbers, such as 0063MX90004qzCN. The field is definitely there, as it's used by Salesforce as an unique identifier for a particular record (Opps, Accounts, Users etc.). It should have 'internal' in the name. Your IT department should have made it visibile though (or perhaps it's just me) 🙂

Good luck!

Alice

Alice,

It Worked!!

Though I have to admit I don't know why it should work as the ID also has lowercase and uppercase letters that could also change their case. But somehow it doesn't. Something is different, but I don't know what.

How did you realize this could be a solution?

 

Thanks again for your time in solving this issue.

All the best,

Rob

AliceW
Impactful Individual
Impactful Individual

Rob,

So happy I could help :0) I agree it's a strange behaviour, one field sometimes changing while the other stays the same.

I think I just had a hunch when I realised the Internal ID field matched the last part of the URL.

I now use the same logic for all fields I need an URL for: Accounts, Leads etc.

Best of luck with your reports!

Alice

 

v-lionel-msft
Community Support
Community Support

Hi @AliceW ,

 

For values of the same column, Power BI automatically converts lowercase to uppercase if the values are all the same except for letter case.

 

For example, here’s my original data:

b5.png

Then, in Power BI data view:

b6.png

 

As tested, we can use “Replace Values” feature:

(replace with: blank a1)

b7.png

 

(replace with: url/)

b9.png

b10.png

 

Beyond that, there is no particularly good way.

 

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The plot thickens.

I actually have the code as a separate column ('Internal Code'), except it has 3 additional letters after the 'right' first 15 ones.

I've tried trimming it with LEFT('Internal Code', 15), and it ALSO capitalized all letters except the first one.

What is going on?

And I couldn't figure out what you mean by the blank.. I've tried creating a new column as CONCATENATE(BLANK(),'Internal Code'), but I still got capitalized letters.

I've also, in Power Query, tried creating a new column by trimming the 'Internal Code' to the first 15 characters, and it also capitalized them.

That's a weird behaviour of my favorite software, guys.

Hi @AliceW ,

"And I couldn't figure out what you mean by the blank."
I mean insert a space before a1.
Actually for this problem, there are currently two possible solutions:

1. Use M language

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ 

2. Modify value
Just like I did before, insert the corresponding characters in the column value to distinguish the column value.

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.