PowerBI not respecting case sensitive Id columns (Case changing from PowerQuery to PowerBI)

[davidwc]

Hey everyone,

So I have a problem with data that I have imported from Salesforce. Our AccountId column is normally a 18 character identifier but for this specific object we are only getting a 15 character Id. This is ok becuase the last 3 characters are not needed to provide a unique value.

So my issues arise when trying to trim the 18 character to 15 characters. This Id field is a mix of numbers and upper/lower case letters. When I use a Dax calculated column using the formula:

Id_Short = LEFT([Id],15)

The results are as such: 

Id	Id_Short (Dax Custom Column  = LEFT([Id],15))
001QP00000uutGIYAY	001QP00000uutGI
001QP00000uutGiYAI	001QP00000uutGI

You can see the last character "i" is capitalized.

When going to PowerQuery to acomplish the task I get the same results with using the Split function and removing the extra text column. I also tried using "Text.Start([Id], 15)" as another column "IdShortPQStart" with no luck. I also have them as two different data types one as defined as text and it still does not respect the case.

You can clearly see the lower case "i" in PQ but when data data is refreshed it capatilizes it in PowerBI. It weird to me because it respects the case for the other characters in this column.

Anyone experienced anything like this or would be able to help me out with this issue?

[v-tejrama]

Hi  @davidwc ,

Thank you @MarkLaf  and  @DataNinja777  for the response provided! 

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[v-tejrama]

Hi @davidwc ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you,

Tejaswi.

[v-tejrama]

Hi  @davidwc ,

Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.

Thank you.

[v-tejrama]

Hi  @davidwc ,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

Thank you,

Tejaswi.

[MarkLaf · Accepted solution]

Problem

What you are seeing is the VertiPaq (underlying DAX engine) in action. One of the things it does to save space is encode column values such that only distinct values take up memory. By default, this is done in a case-insensitive way. Here is an article on column encoding if you want more context: https://www.metisbi.co.uk/blog/power-bi-encoding-explained-value-dictionary-and-rle

Take the below table. Column encoding in practice will mean that only one value is saved for each column below, and it's whichever appears first in the data.

Column1	Column2
aB	ab
Ab	AB
AB	Ab
ab	aB

You can verify this. Copy/paste the above into a pbix with Enter Data. In Power Query, it looks as is. When you load the table to the model, though, it looks like the below.

You can see this with your sample, too. Change the order of your two IDs, perform the 15 char trim, and you'll see that it's the version with "i" rather than "I" that gets saved and reused.

Solution

There are two ways to address this that I'm aware of:

1) Trick the engine by adding zero-width spaces to the end of you case-insensitive duplicate IDs to force them to be distinct even when case-insensitive. Here is a Power Query transformation that does this:

Sample

Id
001QP00000uutGi
001QP00000uutGI
aB
Ab

let
    Source = Sample,
    GroupFix = 
    Table.Combine( Table.Group(
        Source, {"Id"}, {{
            "fixed", 
            each Table.FromRecords( 
                Table.TransformRows( 
                    Table.AddIndexColumn(_,"i"), 
                    (row) => Record.RemoveFields( 
                        Record.TransformFields( row, {
                            {"Id", each _ & Text.Repeat( Character.FromNumber(8203) ,row[i])}
                        } ), 
                        "i" 
                    ) 
                ), 
                Value.Type(Source) 
            ) , 
            Value.Type(Source)
        }},
        GroupKind.Global,
        Comparer.OrdinalIgnoreCase
    )[fixed] )
in
    GroupFix

fix and original Sample (in PQ)

original Sample (in model)

fix (in model)

2) Change the collation property of your model. I think you need a premium license for this to work, as an XMLA endpoint is needed. The steps are:

1. Open your model in Tabluar Editor
2. Click on "Model" and scroll down to Options > Collation
3. Enter a case-sensitive collation code, something like: Latin1_General_100_CS_AS
4. Now, deploy as a new model to your XMLA Endpoint with the Deployment Wizard (toolbar > Model > Deploy... OR shortcut: F6)
   (you unfortunately cannot just hit save and push this change to the model)
5. If successful, you should now have a new model that is case-sensitive in your workspace. You can download the pbix and continue development.

[DataNinja777]

Hi @davidwc ,

This issue is caused by the Power BI data model engine, which is case-insensitive by default. It treats characters like 'i' and 'I' as the same value for uniqueness. When it loads data, it may see two values like "uutGi" and "uutGI" as identical, pick one version of the casing, and apply it to both rows. This is why you observe the correct case in Power Query, but it changes after being loaded into the data model.

You can work around this by creating a new column in the Power Query Editor that forces the engine to see the values as unique. First, create your 15-character ID column, which we can call IdShort. Then, add a new custom column using the "Add Column" tab. In the formula box for this new column, enter the following M code. This will create a new, truly unique string that preserves the original case by appending a separator and the final character.

[IdShort] & "|" & Text.End([IdShort], 1)

After applying this step and loading the data, you will have a new column in your model, let's call it IdShort_CaseSensitive, which contains values like 001QP00000uutGi|i. Since this is now a distinct string, the engine will store it with the correct case intact. For use in visuals, you will want to remove the suffix. You can do this by creating a new calculated column using DAX.

Display IdShort = LEFT('YourTable'[IdShort_CaseSensitive], 15)

This new DAX column, Display IdShort, can be used in your tables and charts, and it will correctly display the case-sensitive 15-character ID because it is based on the column where the casing was successfully preserved. Use the IdShort_CaseSensitive column for any relationships and the Display IdShort column for visualization purposes.

Best regards,

[davidwc]

Thank you for generating an AI response for me. But this did not work - PowerBI still did not respect the case sensitivity for the column. 

I was not able to solve the case sensitivtiy issue between PowerBI and PQ. But I was able to resolve my issue. 

For anyone else running into this, I was able to make a formula field in Salesforce on the object and use the fomula "CASESAFEID(Account.Id)" which provided the full 18 character Id. The 18 character version of the Id field is unique regardless of case for all of our Accounts.