How to generate a Relational Key for a Text Column using Unicode of Characters in Power Query

How to create a Custom Key for a Dimension Column using the Unicode of Characters in Power Query
Scenario:
Suppose we have a Dimension Table having the text based Columns like Product or Country Name with no Id Column in that table.

Now if we want to generate a Key for Product or Country, which we can be used as a Key in the Power BI Model.

We can implement this using the combination of List.Accumulate() and Character.ToNumber() Functions.

The List.Accumulate () will helps iterate through each Character of Product or Country Name and then Character.ToNumber () will generate UNICODE of that Character.

Now we will generate Product Key using the above functions as per below:

ProdKey :

= Table.AddColumn(ds_Dataset, “Product_Key”, each

List.Accumulate (Text.ToList (Text.Upper([Product])) , “”,

(temp, CurVal)=> temp & Number.ToText(Character.ToNumber(CurVal), “0”)))

#Result:

#Notes:

here, List.Accumulate contains two internal variables that can be used to iterate over the elements of a list (like an array) and assemble a new value.

The “temp” variable holds temporary value that we can build on each each iteration, and the “CurVal” variable represents the value of the current element.

The final code takes the output from “Text.ToList” and builds a List object from the characters in the “Product” field on that row.

Next, List.Accumulate iterates over each character where our code uses “Character.ToNumber” over the current character to convert it to numeric(UNICODE) form.

finally, “Number.ToText” will convert the UNICODE to text form which enables us to concatenate with Previous value(in temp).

In the above example of Product_Key, we generated a Key by concatenating the Unicodes of each character of Product. Hence, the Key looks bigger in size.

Now lets try to create a Key for Country, by using the Sum of all Unicodes of each character of Country.

CountryKey :
= Table.AddColumn(ProdKey, “Country_Key”, each

List.Accumulate( Text.ToList(Text.Upper([Country])), “”,

(temp, CurVal)=> (if temp=”” then 0 else temp) + Character.ToNumber(CurVal)))

#Result: