Forum Discussion
The supposed case sensitive problem
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_CharOutput:
Pete
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 🙂
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
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
That is exactly my problem 🙂
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.
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
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