Forum Discussion
ValeriaBreve
Post Partisan
Post PartisanReplace 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...
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 addV2I 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
Super User
Super UserHi, 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