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

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.

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

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.