Forum Discussion
courtney11
2 years agoNew Member
Replace string of characters (data type text) with equivalent whole number
I have a custom column that I am attempting to populate with mapped customer numbers. If the mapped customer number exists, use that, else use the AccountNo or DeliveryCustomer fields (Text) and co...
- 2 years ago
Check this:
I've changed your Character.ToNumber([AccountNo]) to this, which converts every character to number and combine all numbers as text. If you want to make it really unique - you can add some random number before combining.
Text.Combine(List.Transform(Text.ToList([AccountNo]), (x)=> Text.From(Character.ToNumber(x))))Result
Whole code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxC4MwEIX/Sshs6yVp1I5SQZBOOnQQh1QDFTRaa1r672tUUunUQLh3cO97l+Q5Tjo9KNFgBxOXuBQoMRIAppKAOYwDmxr0cwEXTo5TqeRrsdON/8BgxgTAuRV7umtXcxQzoFtdduoph1FWaOyQVvVdS6R0e5XDHJMJkU2jawSbI3wTQT3LyS6h1bZHp7/AxwVMv2AS+N4KitOQmFfFg+hvdSlQVD/6RrzReazMCtMfcU43qig+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, #"Revenue Date" = _t, Nominal = _t, InvoiceNo = _t, #"Lines.Renewal Deferrals.AccInvoiceNo" = _t, AccountNo = _t, DeliveryCustomer = _t, #"Customer Mapping.Account#" = _t, #"EXPECTED RESULT" = _t]), TrimColumnNames = Table.TransformColumnNames(Source, each Text.Combine(List.Select(Text.Split(_, " "), (x)=> Text.Length(x) > 0), " ")), ReplaceBlankToNull = Table.TransformColumns(TrimColumnNames, {}, each if Text.Trim(_) = "" then null else _), Ad_Result = Table.AddColumn(ReplaceBlankToNull, "Result", each if [#"Customer Mapping.Account#"]<> null then [#"Customer Mapping.Account#"] else if [AccountNo]<>null then Text.Combine(List.Transform(Text.ToList([AccountNo]), (x)=> Text.From(Character.ToNumber(x)))) else if [DeliveryCustomer]<>null then Text.Combine(List.Transform(Text.ToList([DeliveryCustomer]), (x)=> Text.From(Character.ToNumber(x)))) else 0, Int64.Type) in Ad_Result
courtney11
2 years agoNew Member
Here you go:
| TEXT | TEXT | WHOLE NUMBER | WHOLE NUMBER | |||||
| Source | Revenue Date | Nominal | InvoiceNo | Lines.Renewal Deferrals.AccInvoiceNo | AccountNo | DeliveryCustomer | Customer Mapping.Account# | EXPECTED RESULT |
| Journal | 1/1/2021 | 1000 | J000003503 | 0 | ||||
| Renewal | 12/1/2021 | 1430 | 108055 | 108055.2-m | DG302 | DG302converted to unique number | ||
| SaaS | 2/1/2023 | 1470 | 2602 | SWA02 | SWA02 Converted to unique number | |||
| SaaS | 9/1/2022 | 1470 | 1876 | GRA11 | Graphica Display Ltd | 2000552 | 2000552 |
dufoq3
Community Champion
2 years agoCheck this:
I've changed your Character.ToNumber([AccountNo]) to this, which converts every character to number and combine all numbers as text. If you want to make it really unique - you can add some random number before combining.
Text.Combine(List.Transform(Text.ToList([AccountNo]), (x)=> Text.From(Character.ToNumber(x))))
Result
Whole code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxC4MwEIX/Sshs6yVp1I5SQZBOOnQQh1QDFTRaa1r672tUUunUQLh3cO97l+Q5Tjo9KNFgBxOXuBQoMRIAppKAOYwDmxr0cwEXTo5TqeRrsdON/8BgxgTAuRV7umtXcxQzoFtdduoph1FWaOyQVvVdS6R0e5XDHJMJkU2jawSbI3wTQT3LyS6h1bZHp7/AxwVMv2AS+N4KitOQmFfFg+hvdSlQVD/6RrzReazMCtMfcU43qig+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, #"Revenue Date" = _t, Nominal = _t, InvoiceNo = _t, #"Lines.Renewal Deferrals.AccInvoiceNo" = _t, AccountNo = _t, DeliveryCustomer = _t, #"Customer Mapping.Account#" = _t, #"EXPECTED RESULT" = _t]),
TrimColumnNames = Table.TransformColumnNames(Source, each Text.Combine(List.Select(Text.Split(_, " "), (x)=> Text.Length(x) > 0), " ")),
ReplaceBlankToNull = Table.TransformColumns(TrimColumnNames, {}, each if Text.Trim(_) = "" then null else _),
Ad_Result = Table.AddColumn(ReplaceBlankToNull, "Result", each if [#"Customer Mapping.Account#"]<> null then [#"Customer Mapping.Account#"]
else if [AccountNo]<>null then Text.Combine(List.Transform(Text.ToList([AccountNo]), (x)=> Text.From(Character.ToNumber(x))))
else if [DeliveryCustomer]<>null then Text.Combine(List.Transform(Text.ToList([DeliveryCustomer]), (x)=> Text.From(Character.ToNumber(x))))
else 0, Int64.Type)
in
Ad_Result
- courtney112 years agoNew Member
This worked, thank you for the help!
- dufoq32 years ago
Community Champion
You're welcome.