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

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. 🙂