Forum Discussion

courtney11's avatar
courtney11
New Member
2 years ago
Solved

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...
  • dufoq3's avatar
    dufoq3
    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