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
This worked, thank you for the help!
dufoq3
Community Champion
2 years agoYou're welcome.