Convert key in text to Integer for optimization

[Omega]

Hi, 

I have created tables in Power BI, in which the keys in each table is in text. After analyzing the model, I noticed that the key columns are consuming a lot of size causing performance issues. 

I want to convert those columns from TEXT to INT, so when stored, the model will be lighter. How to do so? 

Key example:

USA_CAL_F41241

USA_CAL_FG3124

CAN_ONT_FF31921

Thanks!

[rubymaya]

Hi,

this is what im looking for, but can anyone tell me what is

9223372036854775807

 in the code?

Thanks 

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @Anand24  provided, and i want to offer some more information for user to refer to.

hello @Omega , you can create a custom column.

let
        ListChars = Text.ToList([your column name]),
        ListNumbers = List.Transform(ListChars, 
            each Character.ToNumber(_)),
        HashNumber = List.Accumulate(ListNumbers, 
            0,
            (state, current) => 
                Number.Mod((state * 31 + current), 9223372036854775807))
    in
        HashNumber

Output

You can also refer to the following code in advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg12jHd29Il3MzE0MjFUitVBEnI3BoqBhZwd/eL9/ULi3dyMDS2NIMocnZxd3dyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
        ListChars = Text.ToList([Column1]),
        ListNumbers = List.Transform(ListChars, 
            each Character.ToNumber(_)),
        HashNumber = List.Accumulate(ListNumbers, 
            0,
            (state, current) => 
                Number.Mod((state * 31 + current), 9223372036854775807))
    in
        HashNumber),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omega]

This is what I'm looking for. How can I do the same with calculated column? 

[Anand24]

@Omega ,
You can't have INT type for alpha-numeric fields/columns. The whole point and sense of INT (Integer) datatype is that the field has to be numeric.

If you have any text characters in your field, the data type is correctly TEXT.

[Omega]

That I'm already aware of. I need a way to hash those keys so they can show as INT. Any idea?

[Anand24]

@Omega ,
Yes you definitely can.

Go to Power Query Editor, Rt. click on Key -> Split by Non-Digit to Digit -> Use text.combine to add numeric digits at the front of key (I have added 999 to the start) -> Change type to whole number -> keep only the new key column -> rename it

I have attached the pbix for your reference.

Here's the result:

[Omega]

That doesn't work, it just split the key into multiple columns depending if there is a number or not. Assume that my key, is purely text, it convert but rather create a new empty column. 

[Anand24]

@Omega ,
How are you going to hash if the key is purely text?
Can you please provide expected output with different scenarios - only text, only numbers, text followed by numbers, numbers followed by texts, etc. ?