Upper case issue

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
Hello, I received a duplicate key error when refreshing data from SalesForce. One row had "....ELHND" and another had "....ELHNd". I am wondering which stage saw two as the same: Power Query, Power Pivot, or Excel?

We cloned the record and deleted the original and SF assigned a new and different ID.

I was just wondering if there was something I could do to prevent this from happening again?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
When Power Query sends data to Power Pivot it messes up with case sensitivity. I've sent a from on that already but still waiting for an answer.

Have a look at my blogpost on it. Edited a solution for you at the bottom there :-)
 
Last edited:
Upvote 0
Imke - we experienced this again while getting data from Salesforce and I think therein lies the issue: Salesforce treats the IDs as different, so within their world, it doesn't freak out, BUT when trying to use that ID piped through Power Query and Power Pivot, the cases are treated as the same.

So, my IT director asked me if I can make the field a string variable, which I don't think is possible. Thoughts?

(BTW - this would not be an issue if Salesforce would allow to grab more than 2000 rows of data from reports, instead, we have to use individual objects and reconnect them in (my case) Power Pivot)
 
Upvote 0
Power Pivot definitely isn't the right place to stitch together data that has been cut due to what you describe. Power Query can do that better.

So instead of loading into the data model/Power Pivot, you would just load your denormalized table into an Excel sheet that then feeds your pivots and charts. So this is your restriction then: Your output table(s) cannot be longer than 1,05 Mio rows. (Although there also is an unsupported VBA Workaround to create csv, but...)

So will your tables be longer than 1 Mio rows?
Any chance you share your workbook / datamodel? This would make things easier.
 
Upvote 0
Sharing isn't a restriction?

I'll just have to restructure my current reports to do this - which doesn't seem THAT painful, but I just have to redo all my relationships. Maybe a 1-2 day project.
 
Upvote 0
Sorry, I'm a bit slow today.

Just thought that this already was a 1 workbook solution before. So if you've shared that before - why problem now. How can your reports now be in a different workbook than your PowerPivot source data? I think I'm lost...

But if you say that redoing your reports would take you 1-2 days, wouldn't it then be easier to create the surrogate numbered keys instead? Of course, the text fields would still not show the case sensitivity, but your connections would be correct.
 
Upvote 0

Forum statistics

Threads
1,224,118
Messages
6,176,484
Members
452,730
Latest member
palsmith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top