Forum Discussion
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 convert that to a unique number.
Issue is that I keep getting an error saying that the Text field cannot be converted to number when we have to use AccountNo or DeliveryCustomer.
I have tried the following functions with no luck converting the text to a number:
Character.ToNumber
Number.FromText
Example data that I would like to convert to unique number:
JOH06
STR04
Ex statement throwing error when the result is to use the ID:
if [#"Customer Mapping.Account #"]<> null then [#"Customer Mapping.Account #"]
else if [AccountNo]<>null then Character.ToNumber([AccountNo])
else if [DeliveryCustomer]<>null then Character.ToNumber([DeliveryCustomer])
else 0
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
5 Replies
- dufoq3
Community Champion
Hi, could you provide sample data (lets say 3 rows) for every column? And also expected result based on this data?
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
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
