Replace string of characters (data type text) with equivalent whole number

[courtney11]

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

[dufoq3]

Hi, could you provide sample data (lets say 3 rows) for every column? And also expected result based on this data?

[courtney11]

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 · Accepted solution]

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]

This worked, thank you for the help!

[dufoq3]

You're welcome.