Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

Replace a number in a string with another one

Hello, I have a column that contains a number (a year), which is NOT FIXED - it could be any year. I need to replace it with the values of another column:     In this case, what in the col...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Valeria,

     

    Paste this into a new blank query using Advanced Editor to see the steps to take:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSAVEmSrE60UpeiXkKbs4KwQWpeSkKGuiyvolFCm7JSLImEFkLsKxLajKqrClY1thUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First_Part = _t, CalculatedYear = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"First_Part", type text}, {"CalculatedYear", Int64.Type}}),
    splitColByDelim = Table.SplitColumn(chgTypes, "First_Part", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, true), {"First_Part.1", "First_Part.2"}),
    addV2 = Table.AddColumn(splitColByDelim, "First_Part_v2", each if try Value.Is(Number.From([First_Part.1]), type number) otherwise false = true then Text.From([CalculatedYear])
else [First_Part.1] & " " & Text.From([CalculatedYear]))
in
    addV2

     

    I basically just split the original column by delimiter ( " (" ) then did a check to see if the original column value was numerical. If yes, then use [CalculatedYear], if no, then concatenate the split original with [CalculatedYear].

     

    Example output:

     

    Pete

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago

Hi, ValeriaBreve suppose that this is the only "number" in the string. Then

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU3DLL0pNTiwuUTAyMDLWUNJRAtImSrE60SCGMZwfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First_Part = _t, CalculatedYear = _t]),
    numbers = List.Buffer({"0".."9"}),
    r = Table.ToRecords(Source),
    txf_field = 
        List.Transform(
            r,
            (x) => 
                Record.TransformFields(
                    x,
                    {"First_Part", (y) => [s = Text.Select( y, numbers), u = Text.Replace(y, s, x[CalculatedYear])][u]}
                )
        ),
    result = Table.FromRecords(txf_field)
in
    result

 

 

 

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
3 years ago

AlienSx Hi, this works as well! However, I have a hard time to follow the steps. I did learn the function Record. TransformFields that I did not know existed! But I am not good enough for now to follow through the function in function step. I will get there in time ;-).

Thanks!

Kind regards

Valeria

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    Hello, ValeriaBreve M functions handbook. 

     

    [s = Text.Select( y, numbers), u = Text.Replace(y, s, x[CalculatedYear])][u]
// is just a replacement to 
let
  s = Text.Select( y, numbers),
  u = Text.Replace(y, s, x[CalculatedYear])
in 
  u

     

    You may think of a query as a record with step_names = record field names. You may use "let ... in" instead - nothing wrong with that. 

    Text.Select (y, numbers) selects only chars "0".."9" from your string. Result is a concatenation of all "numerical" chars in your string. Next step replaces this substring with a text value from your CalculatedYear column. 

    p.s. I've just noticed that CalculatedYear is Int64.Type. So please wrap x[CalculatedYear] in Text.From. 

    • ValeriaBreve's avatar
      ValeriaBreve
      Icon for Post Partisan rankPost Partisan
      3 years ago

      AlienSx thank you so much for taking the time to explain!!!!!! It is much clearer now. 🙂