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 column [First_Part] reads 2023 should be replaced with the corresponding CalculatedYear - and the additional text should be kept.

 

E.g., Jan Forecast Spend(2023 should be transformed into Jan Forecast Spend (2025

 

And again it should all be flexible - I cannot use 2023 as a reference... it's replacing the only number that will be in that column.

 

Can you please help me?

 

Thanks!

Kind regards

Valeria

 

 

 

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