The supposed case sensitive problem

[Bokazoit]

I have this data were the identity column is a text base column, and from my reading Power BI /Query is case sensitive, but not from my view. In power BI desktop it looks like this:

And that is ofcourse the same id

But in power query it is treated as case sensitive and I ofcourse only get one row:

I need to handle that ID in desktop as case sensitive. How to do that?

[BA_Pete · Accepted solution]

Try this example code in Power Query. It converts your text values into separate unicode characters, so will be unique between your different text case examples:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0it3DnZRitUBssKT4SznYjgrOAXCKk8uToGLAWVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AGILLIC_ID = _t]),
    addAgillic_Char = Table.AddColumn(Source, "AGILLIC_CHAR", each List.Transform(Text.ToList([AGILLIC_ID]), each Character.ToNumber(_))),
    extractAgillic_Char = Table.TransformColumns(addAgillic_Char, {"AGILLIC_CHAR", each Text.Combine(List.Transform(_, Text.From), "-"), type text})
in
    extractAgillic_Char

Output:

Pete

[BA_Pete]

Hi @Bokazoit ,

I don't think the issue you're experiencing has anything at all to do with case sensitivity.

Can you give me some more detail around what the actual issue is please? Is it that you have more rows in PBI Desktop (the model) than you do in Power Query? If so, then the model is downstream of Power Query in this context so, if the rows are correct in the model, then they are also correct in Power Query and it's just a display issue within the Power Query preview.

Pete

[Bokazoit]

Thank You for taking You time to reply

I do believe it do have to do with lack of case sensitivity. I have two tables where the ID is as Shown above. In desktop it looks as shown in the first picture, but in power query it looks like this:

So in desktop each row is handled like the id is '.WCSD' while it should handle each ID as a seperate ID if handled with case sensitive data so to speak.

So when trying to join the two tables as a one to many I get an error saying that there is identical ID's in the column which is the case in desktop but not in power query

[BA_Pete]

Ah, ok. Your original screenshots just showed all the values as upper case, so was struggling to understand what you meant.

So, for my understanding, the issue is: You have values in Power Query that represent distinct information, but the only thing that makes them distinct is the text case that they are written in. Therefore, once you send the data from Power Query which IS case sensitive to PBI Desktop (the model) which ISN'T case sensitive, then you lose the ability to distinguish between these distinct values based on case. Is that correct?

If that's correct, then please let me know how you wish to use/display these values in the model as there may be a couple of different ways to handle this depending on what you actually need/want to do with these values.

Pete

[Bokazoit]

Perhaps I forgot to tell what I need. I need to create a uniqe id so that I can join the two tables, and the only true id I have is that case sensitive id.

[BA_Pete]

Ok, so if you're doing the join (merge) in Power Query then it should be fine? Or do you mean you want to relate the tables in the model?

Pete

[Bokazoit]

I wants to be able to relate in model 🙂

The first table is a "customer" table and the second is a subscription tabel, a one-to-many table realation

[BA_Pete]

Ok. Give me some time to put together a possible solution for you.

Pete

[BA_Pete · Accepted solution]

Try this example code in Power Query. It converts your text values into separate unicode characters, so will be unique between your different text case examples:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0it3DnZRitUBssKT4SznYjgrOAXCKk8uToGLAWVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AGILLIC_ID = _t]),
    addAgillic_Char = Table.AddColumn(Source, "AGILLIC_CHAR", each List.Transform(Text.ToList([AGILLIC_ID]), each Character.ToNumber(_))),
    extractAgillic_Char = Table.TransformColumns(addAgillic_Char, {"AGILLIC_CHAR", each Text.Combine(List.Transform(_, Text.From), "-"), type text})
in
    extractAgillic_Char

Output:

Pete

[Bokazoit · Accepted solution]

Thank you. I ended up using a concat of that ID and Email since even that smart solution You gave, did give non-uniques. But thanks again for trying 🙂

[Bokazoit]

That is exactly my problem 🙂