Forum Discussion
Replace Value But Not the First Match
Hi, I am trying to clean a manual input file. So, I have a cell of one column with content like this: abc123abc456abc789 (1) and I want to replace the "abc" to become like this: abc123, abc456, abc...
Solve your two problems at one go
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyBpImpmZA0tzCUilWByyMIQBRBxaIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))), #"Removed Nulls" = List.Transform(Table.ToRows(#"Split Column"), each Table.FromRecords({List.Accumulate(_, [], (s,c) => if not (c is null) then Record.AddField(s, Text.Select(c, {"0".."9"}), c) else s)})), #"Combined Tables" = Table.Combine(#"Removed Nulls") in #"Combined Tables"PS: my favorable solution with regular expression
Solve your two problems at one go
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyBpImpmZA0tzCUilWByyMIQBRBxaIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
#"Removed Nulls" = List.Transform(Table.ToRows(#"Split Column"), each Table.FromRecords({List.Accumulate(_, [], (s,c) => if not (c is null) then Record.AddField(s, Text.Select(c, {"0".."9"}), c) else s)})),
#"Combined Tables" = Table.Combine(#"Removed Nulls")
in
#"Combined Tables"
PS: my favorable solution with regular expression