Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post 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
Post Partisan
3 years ago

BA_Pete Hi Pete, thanks! This works very well! I was trying to understand the code and there is something I cannot grasp.

When you write:

each try Value.Is(Number.From([First_Part.1]), type number) otherwise false = true

I do see it works, it converts the record from the "try" expression into the right "true/false" string without expanding any columns - but could you please elaborate into more details? I cannot figure it out.

 

Thanks!!!

Kind regards

Valeria

 

 

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

Haha, yes, when you highlight it like that it does look a bit odd "otherwise false = true" 🙂

 

It actually breaks down like this:

Number.From( ... ) tries to convert your value to a number. It outputs an error if it's not.

Value.Is( ... , type number) tries to turn the result of the above into a TRUE/FALSE output to satisfy the IF...THEN structure. The problem with this is that if your value isn't a number, it evaluates to an error due to the error thrown by our Number.From step. So =>

try ... otherwise false handles this error by saying "TRY to get a non-error value from the above OTHERWISE give a FALSE output instead".

We now have an expression that always evaluates to either TRUE or FALSE with no errors, so we can use the IF...THEN...ELSE structure around it. So we end up with this:

 

if [the above evaluation] = true then [do this] else [do this]

 

Hope this makes sense.

 

Pete