Wierd changing of data on import

[masplin]

Hi. I am trying to do some matching between Salesforce data and our in house system. I'm using email and last name.

What is odd is Salesforce uses combinations of upper and lower case such that 2 different records look like duplicates if you don't compare case sensitive.  e.g. one user is 0034L00000nBzZp  another is 0034L00000nBzzP

So in power query I have user whose Salesforce ID is showing as 0034L00000nBzzP

If I import that one row into my model I get this

However if I import the whole of my salesforce table and filter in dataview in Power BI I get this. Last 4 digits have changed from BzzP to BzZp which is a different persons ID???

I then filtered in Power Query for Contact ID contains "0034L00000nBz" Imported and got  BzZP which is a different person again.

I'm completely mystified how the case of the data field can change on import?  Makes it impossible to use. 

I'm struggling to make a version I can share as it seems if I filter the Salesforce input table for All the "Amanda"s or All surname begining with L the problem goes away. Only when I have the whole table is the case changing!!! 

I've taken out all the matching code so left with not much

let
    Source = Excel.Workbook(File.Contents("U:\SharePoint\Limerston Capital\Limerston All Stars - Data Analysis and Research\FTA\All_SF_Contacts.xlsx"), null, true),
    All_SF_Contacts_Mike_Asplin_May_Sheet = Source{[Item="All_SF_Contacts_Mike_Asplin_May",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(All_SF_Contacts_Mike_Asplin_May_Sheet, [PromoteAllScalars=true]),
    #"Sorted Rows1" = Table.Sort(#"Promoted Headers",{{"Account Name", Order.Ascending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows1",2),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Account Name", type text}, {"Account Type", type text}, {"Full Name", type text}, {"Job Role", type text}, {"Billing Zip/Postal Code", type text}, {"Marketing Status", type text}, {"CPD Training", type date}, {"Phone", type text}, {"Email", type text}, {"Empirical Primary Key", Int64.Type},  {"Column12", type any}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Empirical Primary Key"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Errors", "Full Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Full Name.1", "Full Name.2", "Full Name.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Full Name.2", "First Name"}, {"Full Name.3", "Last Name"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Account Name", "First Name", "Last Name", "Billing Zip/Postal Code", "CPD Training", "Phone", "Email", "Empirical Primary Key", "Contact ID"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"First Name", Order.Ascending}})
in
    #"Sorted Rows"

Any advice much appreciated as its complely bizarre

Mike

[lbendlin]

That's why you always, always, always MUST use Case Safe IDs.

Salesforce is case sensitive.  Power Query is case sensitive. Power BI is NOT case sensitive.

[Wilson_ · Accepted solution]

Hey Mike,

Case sensitivity has always been funky and this is not the first time I've heard about issues with Salesforce specifically. So much so that people have written Power Query functions to convert Salesforce IDs to a more Power BI friendly number. (This is one such function.) Maybe incorporating something like this might help you.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[masplin]

Well there you go I leant something today. Thanks so much as thought I was going mad.  So actually if you want to pam SF to any other system you are better to put the other system ID in SF than the other way round.

[Wilson_]

Yeah, just kind of a weird quirk of joining a case insensitive system with a case sensitive system!