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 🙂
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 realationOk. Give me some time to put together a possible solution for you.
Pete
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