Replace text within value based on another column

Hi NiugeS ,

Try this code on Advanced Editor (I assumed you already have the User tables loaded):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVK7boMwFP2VW2aSGEP6WqIqGUrUZqjaiXqgcCUQxo6MUcTSb68NVDKQTMj3npcPThIvPni+d0g1ms9e1jUKbQfYlApz+MCm5dpjfuIFNIzMhpINCTeUUGIOn0UqKuhkC8lvmmWyFbrMn78aVAHzIf5uCaEPnANHvUBQBrkEXSBcpKrWE7WjLMScf5Q4JbihqBPqJOGs5A/Heg0TV7C2EYN9KnqXAvl5Nye8lxXOEK5R5Bi9YgepSbda3M2YxJAZESswQfbyzm6mHf1rX+tz2eByFl6ZmTSr3r6RSmN+Z+SHgk2lPryIXOHFH5K5uCFbtL03+OBpdu9FrwEbN1Z6pIbb8cHQW9Se+WjzndA8N1sJXEpdmN9cNruR8NZm3S0EY38=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Date", type date}, {"Comment", type text}, {"Desired Result", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Desired Result"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Comment", Splitter.SplitTextByDelimiter("[~accountid", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comment"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Comment", type text}}),
#"Added Index1" = Table.AddIndexColumn(#"Changed Type2", "Index.1", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "XPTO", each try Text.Range([Comment], 1,
Text.PositionOf([Comment], "]")-1)
otherwise null),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"XPTO", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"XPTO", Text.Clean, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Cleaned Text", {"XPTO"}, User, {"User"}, "User", JoinKind.LeftOuter),
#"Expanded User" = Table.ExpandTableColumn(#"Merged Queries", "User", {"Name"}, {"Name"}),
#"Sorted Rows" = Table.Sort(#"Expanded User",{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"XPTO"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Comment", Splitter.SplitTextByDelimiter("]", QuoteStyle.Csv), {"Comment.1", "Comment.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Comment.1", type text}, {"Comment.2", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "Text", each if [Name] = null then [Comment.1] else
[Name] & [Comment.2]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Comment.1", "Comment.2", "Name"}),
#"Sorted Rows1" = Table.Sort(#"Removed Columns2",{{"Index.1", Order.Ascending}}),
#"Added Custom2" = Table.AddColumn(#"Sorted Rows1", "Index_", each [Index] + ([Index.1] / 10)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom2",{{"Index_", type number}}),
#"Sorted Rows2" = Table.Sort(#"Changed Type4",{{"Index_", Order.Ascending}}),
#"Added Custom3" = Table.AddColumn(#"Sorted Rows2", "Custom", each Table.Group(#"Sorted Rows2", {"ID", "Index"}, {{"Valor", each Text.Combine([Text])}})),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Index.1", "Text", "Index_"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns3", {"ID", "Date"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Duplicates", "Custom", {"Index", "Valor"}, {"Index.1", "Valor"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Filter", each if [Index] = [Index.1] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([Filter] = 1)),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1", "Filter"})
in
#"Removed Columns4"

Ricardo