Forum Discussion
Convert long text containing both letters and numbers into a unique numeric representation.
Issue:
I have a dataset in Power BI where the id column contains long text strings, leading to large file sizes and performance issues.
Requirement:
I want to convert these long text values into numeric representations using ASCII values to optimize storage.
File : https://drive.google.com/file/d/1aqxAbrW_5SqhwT6BurFvJmTLowxoIy7R/view?usp=sharing
Question:
How can I achieve this transformation in Power BI?
Sample Data:
sno id
1 82E04E82273656A57B08BD066C151161B0989643BA84D984B2FA008C0C5831B1&CB4C7A1094C7DE83CB147C7F1177A708E1B86C22AEA70DF68140D27851B4E5C8&79B2F2243B35DA5CE258E625188078F336E037BF1974C567C50D4BCA9FF0AC5A
2 80A63E6BDCDA29F994EA57B8D520C2993C34E8B747A182D08718DF1A4D8419CD&25B68D60718F776135608764E7424A9D6058A6AA92FF6DFE663BF9314E4D11B0&F54A9C00D28F2EAE1C5974C36B5E08E9313CD5FDBED24EFB2AC946ED30688201
3 7C8C85AC61CE1173977F34984152C4475A68738961E6ADE68BC351944D707C79&8615769D861931B99D84D380631ADB6C64DCD1B50B094D3489F7EC19A5213F38&0568A665804CDBCB4ED0BBECF79346166068391A94486E4A733EEEDE4531F826
4 7C8C85AC61CE1173977F34984152C4475A68738961E6ADE68BC351944D707C79&8615769D861931B99D84D380631ADB6C64DCD1B50B094D3489F7EC19A5213F38&0568A665804CDBCB4ED0BBECF79346166068391A94486E4A733EEEDE4531F826
5 7C8C85AC61CE1173977F34984152C4475A68738961E6ADE68BC351944D707C79&8615769D861931B99D84D380631ADB6C64DCD1B50B094D3489F7EC19A5213F38&0568A665804CDBCB4ED0BBECF79346166068391A94486E4A733EEEDE4531F826
6 7C8C85AC61CE1173977F34984152C4475A68738961E6ADE68BC351944D707C79&8615769D861931B99D84D380631ADB6C64DCD1B50B094D3489F7EC19A5213F38&0568A665804CDBCB4ED0BBECF79346166068391A94486E4A733EEEDE4531F826
7 751B09CFEA7C1A8F5A38FE4919A13DFEE2EC5890725E3BD343C30635EF9919F9&637CDCB7A4504AB1BB6F6FFF8754939D3567468260B4E6A191C1402C7900C5C8&DAC9F39E6380F9168657B00B895B34FFB321DC9B3350D58E59B2E3ED476D2E05
8 759CF89B29079A7E2E1011F745B2D8D29F1DCDEC71A7062E82D2DE4221A2DC24&3D5DDD07D27F910EA04A42F9634F9AAACD6A698785416EF7B2BCCB032482B51B&D373A001A11825BB446682A1349DFEBE14F4966C51CEF806C57FA6DCBDABF407
Hi sudhisami_azure , You could use a calculate column for this could you please try this
Create a New Column:
- In the ribbon, select Modeling > New Column.
Dax Formula
NumericHash =
SUMX(
GENERATE(
ADDCOLUMNS(
MID([id], SEQUENCE(1, LEN([id])), 1),
"CharValue", UNICODE([Value])
),
ROW("Position", ROW_NUMBER())
),
[CharValue] * POWER(31, LEN([id]) - [Position])
)
Try thisIf this post helped please do give a kudos and accept this as a solution
Thanks In AdvanceHi sudhisami_azure,
Thank you for reaching out to the Microsoft Fabric community.
I understand that you are looking to convert long text values in your Power BI dataset into numeric representations using ASCII values to optimize storage and improve performance.
Below is the M-Query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZXLdWApDERz8boX6C+W6JeEp8OY/Ee2e5LoFe8dOKiqBJfPzw/4+PXh2IfbEY1U9InF8aijmiAACnGuX2WK51zXOXDeOZ4nxQkC/vn3HNQMTntw7g7VThnAljYAZs+ON4RrIr7evxp14FNoLhDckv6zi93dHXGLkdSTbBRvRQH3Yz5E2ocsBq5xilrKKY58d+a8lPfx+9fnB36ZOk+pNSrr4Z17ub+ceQmexHspaT2H8Wp2rOMGXgOPyxlu1o8clFAvPTs5ZgokuiuV2xj53Z0Rf/rexRmtaVWKuQTcXLDB/ewysmvzrF0f7NeQ8iWfNKQ3mF1PWTIVXcg9gS8vaxcddccD36ZoTVl6urxUyN5c6ZoN8bYEBJPZ5KkbbbOg9VWrR5LAZS4724v7I8cVxPTWjls67n5xkR8leBWayhsahJxt/E6w37FOuE8QaOhPp47oWlfxw1mx3e86EZ1jl1hBdcXThbfVXZufEXV3NQvBOOq3Kf4bTcnfaEr/RlP2ZUq+AJezWEp4PvLIp/luQaC90o29nLvHUJpi9Sw5Vqv0MgUWLD9ylCwrwx7L4RcQoaMz4yZ86daCw1i37lml+uBCLv9wQzmL0f/pV3vxh27rxjEXNqovGJ/wK0E8E4RQeYNosbdglKVlUxeb1kJcvk35t6l15Du7uu+z9QAHYIwlsLwWiLtPdRosjBUX/oWbDSI8rET+kUMlVXVsMb1qTr+1xjhXV8t972Xp0+vLcAbtscDIjEPIjrGx/jFFRvtewIMFrUQw6wax4fLdeKOBh+8+NrKnavYYpNg83TDrxfCxj9+//wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sno = _t, id = _t]),
AddHashColumn = Table.AddColumn(Source, "NumericHash", each
Number.Mod(
List.Sum(
List.Transform(
List.Zip({ List.Transform(Text.ToList([id]), each Character.ToNumber(_)), List.Reverse(List.Numbers(0, Text.Length([id])))}),
each _{0} * Number.Power(31, _{1})
)
), 1000000007
), type number
)
in
AddHashColumnIve also attached PBIX file below.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
6 Replies
- Akash_Varuna
Super User
Hi sudhisami_azure , You could use a calculate column for this could you please try this
Create a New Column:
- In the ribbon, select Modeling > New Column.
Dax Formula
NumericHash =
SUMX(
GENERATE(
ADDCOLUMNS(
MID([id], SEQUENCE(1, LEN([id])), 1),
"CharValue", UNICODE([Value])
),
ROW("Position", ROW_NUMBER())
),
[CharValue] * POWER(31, LEN([id]) - [Position])
)
Try thisIf this post helped please do give a kudos and accept this as a solution
Thanks In Advance Thanks Akash, could you provide me M-query? Because can not load the long text to file.
v-saisrao-msftCommunity Support
Hi sudhisami_azure,
Thank you for reaching out to the Microsoft Fabric community.
I understand that you are looking to convert long text values in your Power BI dataset into numeric representations using ASCII values to optimize storage and improve performance.
Below is the M-Query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZXLdWApDERz8boX6C+W6JeEp8OY/Ee2e5LoFe8dOKiqBJfPzw/4+PXh2IfbEY1U9InF8aijmiAACnGuX2WK51zXOXDeOZ4nxQkC/vn3HNQMTntw7g7VThnAljYAZs+ON4RrIr7evxp14FNoLhDckv6zi93dHXGLkdSTbBRvRQH3Yz5E2ocsBq5xilrKKY58d+a8lPfx+9fnB36ZOk+pNSrr4Z17ub+ceQmexHspaT2H8Wp2rOMGXgOPyxlu1o8clFAvPTs5ZgokuiuV2xj53Z0Rf/rexRmtaVWKuQTcXLDB/ewysmvzrF0f7NeQ8iWfNKQ3mF1PWTIVXcg9gS8vaxcddccD36ZoTVl6urxUyN5c6ZoN8bYEBJPZ5KkbbbOg9VWrR5LAZS4724v7I8cVxPTWjls67n5xkR8leBWayhsahJxt/E6w37FOuE8QaOhPp47oWlfxw1mx3e86EZ1jl1hBdcXThbfVXZufEXV3NQvBOOq3Kf4bTcnfaEr/RlP2ZUq+AJezWEp4PvLIp/luQaC90o29nLvHUJpi9Sw5Vqv0MgUWLD9ylCwrwx7L4RcQoaMz4yZ86daCw1i37lml+uBCLv9wQzmL0f/pV3vxh27rxjEXNqovGJ/wK0E8E4RQeYNosbdglKVlUxeb1kJcvk35t6l15Du7uu+z9QAHYIwlsLwWiLtPdRosjBUX/oWbDSI8rET+kUMlVXVsMb1qTr+1xjhXV8t972Xp0+vLcAbtscDIjEPIjrGx/jFFRvtewIMFrUQw6wax4fLdeKOBh+8+NrKnavYYpNg83TDrxfCxj9+//wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sno = _t, id = _t]),
AddHashColumn = Table.AddColumn(Source, "NumericHash", each
Number.Mod(
List.Sum(
List.Transform(
List.Zip({ List.Transform(Text.ToList([id]), each Character.ToNumber(_)), List.Reverse(List.Numbers(0, Text.Length([id])))}),
each _{0} * Number.Power(31, _{1})
)
), 1000000007
), type number
)
in
AddHashColumnIve also attached PBIX file below.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
- v-saisrao-msft
Hi sudhisami_azure,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - v-saisrao-msft
Hi sudhisami_azure,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-saisrao-msft
Hi sudhisami_azure,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.