Forum Discussion
Solved
Find and Replace text string in one table using value from another table
Hi I am trying to create a query using M language that allows me to correct text in a certain column in a certain table using data from two columns in another table. I have a table of bank transact...
Hi, prankin , why bother to use such an intimidating chunk of codes given that there already exists a native function List.Accumulate? You might want to try such a solution,
let BulkReplaceFunction = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) => let BufferedFRT = List.Buffer(Table.ToRecords(FindReplaceTable)), Substitution = List.Accumulate(//outer List.Accumulate for iteration DataTableColumn, Table.Buffer(DataTable), (s, c) => Table.TransformColumns( s, {{c, each List.Accumulate(//core function, inner List.Accumulate to find & replace BufferedFRT, _, (x, y) => Text.Replace(x, y[Find], y[Replace]) )}} ) ) in Substitution, Replacements = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZGxDoIwEIZfpeJGcGhwcWxrSRtDSSvSGMLgQKKLC2H3WXw0n8S7Ahowcbn+33eXa5rWdRQznkdJhLVJAIUDghJAcgAoATJxBsIaUJtSOs7MAeQ3YyuZr3w9nkQ6LZhBmY9x7Cwnj7BIMLcHm7MPYJOS/21TVR409d4Hkfbd7d52HTg+RfSCKGLX+EgFRzB0U9vtzOwWE0quyEkGJS2EIK/tpW8XTsniZ07ZeFg24fwuTVOBQnMxiMKQUruwZT9G9JLAe/E/SJnB/zRv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t]), BankRecords = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZfLbuM2FIZf5TTpog0kmodXcSnL9FhTS05kx5k0mEUKGBhggBS9AV3OoxR9lD7KPMmQEikzmcyCBQz70LL9+T+Xn9TDwwVeFBdvH59OsPr15EJkXJRSKl2Vxhj3RvPhN/e8fHz6CP6jfIFiwSj18SUzFO7avrfD3i2VIrRyr+5RxjgE74uHC5YHEgFEXfw9IBJhdGm39mj9l5EoGVAxLrUmmo8onofCBZNR0w/HentrYdfDtr35cUJxPKPGuKwMoWJEiTyUWSCLqq7qZQdNvd/A3WIFd/UWuno4uAuMOrUBGePS5ZqIiSlzM8lplHfVDNDZbgfrYdcBZ1i6b5fhdUJyQednYYhUI1Nl68RZp13Cst1u4fq+O0Dd2Xcw2De323rwnEJol8xYzGTpY2Zc2Ue+zubLmb9u7uEw1P1+bYeJI0xhKkoofRmrQkpKJI7MKrONcIFmhrb9wQ7Luv/JodfQkTVpyH//THiGhUBGZPUiVrowihM6STb/v7WKnNZyVK3cEE1UpLlYNmf686d/wQ5tU/dg310Pdr9PKs3GjhrBY+Swkp2xmT6UNpjDOrkre73bt4eYY1G4i6Gq51DjM2imJ73QuncVbuphVcCy23mFZ84o87xU5hk2059SLMIrYCcqBSdLVRU0AWe6FV8wHp0DW04RDuuhXA+vGwdOfzByMeFmOpZvZJzB/fF4B3xYwfXh3lc5zNBZZBJqWqiEm+laDhvzzG/3be8beLe6hrUdN53XCls942W6lN/gosx5YuvVse4bO4qJxCny9pDAMu3JTQyP6lJ7cEs5g2QEiQSUaUSucmZW1TS72/4Aezsc28ZCs6mHN2kuMfJw5rFMB/LdoqOy5enpD+gef/94+tNfqojWkRRiB+OGEcMmWPb5h0VtDWzg5tJhqRpnMB1+ziUZ8+bNlVUEpx2FZRpOSsPy4Ua8TpNIjAwwv5VNWzbLtJkUZr4lzHVIAPEzKNNWXMH4PN7Nxn4H7sQV7HuiOPcwQRNqJJqF7nc7lg6nyUxLkcm5tflwevzrBJeIlRconwlERSINNTGBlm0kKtG3c/IuNfU/W4WDDjJFVDUZCBWCqJDJTANBenasZnNzBaYC7swCeUX1eLqQQZYolKoI95XSrNDIveARmWkjaZdsvtmQiogqoJTx8YjKNBIe5npktcgbOLb7GgZ2I95+zWSuWiYwjSZCT7cCmWYiEi9p3F3AoR0sdM1F3NhGJ/HntYmkKkFMIGU6CYYjqo8tuB0dGvrz379UVn49dIiGVKF2Ujlrcfc5778A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Account Name" = _t, #"Account Number" = _t, #"Account Type" = _t, #"Branch Reference" = _t, Date = _t, Description = _t, Debit = _t, Credit = _t, Value = _t, Balance = _t]), Invoking = BulkReplaceFunction(BankRecords, Replacements, {"Description"}) in InvokingAs you can see, the very essential part of the definition of the function
BulkReplaceFunction = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) => let BufferedFRT = List.Buffer(Table.ToRecords(FindReplaceTable)), Substitution = List.Accumulate(//outer List.Accumulate for iteration DataTableColumn, Table.Buffer(DataTable), (s, c) => Table.TransformColumns( s, {{c, each List.Accumulate(//core function, inner List.Accumulate to find & replace BufferedFRT, _, (x, y) => Text.Replace(x, y[Find], y[Replace]) )}} ) ) in Substitution,
Hi, prankin , why bother to use such an intimidating chunk of codes given that there already exists a native function List.Accumulate? You might want to try such a solution,
let
BulkReplaceFunction = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
BufferedFRT = List.Buffer(Table.ToRecords(FindReplaceTable)),
Substitution = List.Accumulate(//outer List.Accumulate for iteration
DataTableColumn,
Table.Buffer(DataTable),
(s, c) =>
Table.TransformColumns(
s, {{c, each List.Accumulate(//core function, inner List.Accumulate to find & replace
BufferedFRT, _, (x, y) => Text.Replace(x, y[Find], y[Replace])
)}}
)
)
in
Substitution,
Replacements = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZGxDoIwEIZfpeJGcGhwcWxrSRtDSSvSGMLgQKKLC2H3WXw0n8S7Ahowcbn+33eXa5rWdRQznkdJhLVJAIUDghJAcgAoATJxBsIaUJtSOs7MAeQ3YyuZr3w9nkQ6LZhBmY9x7Cwnj7BIMLcHm7MPYJOS/21TVR409d4Hkfbd7d52HTg+RfSCKGLX+EgFRzB0U9vtzOwWE0quyEkGJS2EIK/tpW8XTsniZ07ZeFg24fwuTVOBQnMxiMKQUruwZT9G9JLAe/E/SJnB/zRv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t]),
BankRecords = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZfLbuM2FIZf5TTpog0kmodXcSnL9FhTS05kx5k0mEUKGBhggBS9AV3OoxR9lD7KPMmQEikzmcyCBQz70LL9+T+Xn9TDwwVeFBdvH59OsPr15EJkXJRSKl2Vxhj3RvPhN/e8fHz6CP6jfIFiwSj18SUzFO7avrfD3i2VIrRyr+5RxjgE74uHC5YHEgFEXfw9IBJhdGm39mj9l5EoGVAxLrUmmo8onofCBZNR0w/HentrYdfDtr35cUJxPKPGuKwMoWJEiTyUWSCLqq7qZQdNvd/A3WIFd/UWuno4uAuMOrUBGePS5ZqIiSlzM8lplHfVDNDZbgfrYdcBZ1i6b5fhdUJyQednYYhUI1Nl68RZp13Cst1u4fq+O0Dd2Xcw2De323rwnEJol8xYzGTpY2Zc2Ue+zubLmb9u7uEw1P1+bYeJI0xhKkoofRmrQkpKJI7MKrONcIFmhrb9wQ7Luv/JodfQkTVpyH//THiGhUBGZPUiVrowihM6STb/v7WKnNZyVK3cEE1UpLlYNmf686d/wQ5tU/dg310Pdr9PKs3GjhrBY+Swkp2xmT6UNpjDOrkre73bt4eYY1G4i6Gq51DjM2imJ73QuncVbuphVcCy23mFZ84o87xU5hk2059SLMIrYCcqBSdLVRU0AWe6FV8wHp0DW04RDuuhXA+vGwdOfzByMeFmOpZvZJzB/fF4B3xYwfXh3lc5zNBZZBJqWqiEm+laDhvzzG/3be8beLe6hrUdN53XCls942W6lN/gosx5YuvVse4bO4qJxCny9pDAMu3JTQyP6lJ7cEs5g2QEiQSUaUSucmZW1TS72/4Aezsc28ZCs6mHN2kuMfJw5rFMB/LdoqOy5enpD+gef/94+tNfqojWkRRiB+OGEcMmWPb5h0VtDWzg5tJhqRpnMB1+ziUZ8+bNlVUEpx2FZRpOSsPy4Ua8TpNIjAwwv5VNWzbLtJkUZr4lzHVIAPEzKNNWXMH4PN7Nxn4H7sQV7HuiOPcwQRNqJJqF7nc7lg6nyUxLkcm5tflwevzrBJeIlRconwlERSINNTGBlm0kKtG3c/IuNfU/W4WDDjJFVDUZCBWCqJDJTANBenasZnNzBaYC7swCeUX1eLqQQZYolKoI95XSrNDIveARmWkjaZdsvtmQiogqoJTx8YjKNBIe5npktcgbOLb7GgZ2I95+zWSuWiYwjSZCT7cCmWYiEi9p3F3AoR0sdM1F3NhGJ/HntYmkKkFMIGU6CYYjqo8tuB0dGvrz379UVn49dIiGVKF2Ujlrcfc5778A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Account Name" = _t, #"Account Number" = _t, #"Account Type" = _t, #"Branch Reference" = _t, Date = _t, Description = _t, Debit = _t, Credit = _t, Value = _t, Balance = _t]),
Invoking = BulkReplaceFunction(BankRecords, Replacements, {"Description"})
in
Invoking
As you can see, the very essential part of the definition of the function
BulkReplaceFunction = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
BufferedFRT = List.Buffer(Table.ToRecords(FindReplaceTable)),
Substitution = List.Accumulate(//outer List.Accumulate for iteration
DataTableColumn,
Table.Buffer(DataTable),
(s, c) =>
Table.TransformColumns(
s, {{c, each List.Accumulate(//core function, inner List.Accumulate to find & replace
BufferedFRT, _, (x, y) => Text.Replace(x, y[Find], y[Replace])
)}}
)
)
in
Substitution,
Hi CNENFRNL
Thanks for the post. It took me a while to fully understand what you did. I also had to slightly modify it because it was replacing the string in the Description column at any position in the string. I needed the replacement process to only replace beginning at the start of the string.
Here's my mod:
let
DataTable = BankRecords,
BufferedFRT = List.Buffer(Table.ToRecords(Replacements)),
Substitution = List.Accumulate(
{"Description"},
Table.Buffer(BankRecords),
(s, c) =>
Table.TransformColumns(
s, {{c, each List.Accumulate(
BufferedFRT, _, (x, y) =>
if Text.StartsWith(x, y[Find])
then
Text.Replace(x, y[Find], y[Replace])
else
x)}}
)
)
in
Substitution
This is so easy though!
You could just do a join:
NewStep = Table.Join(PriorStep, {"Description"}, ReplacementTable, {"NameOfColumnOneOfReplacementTable"}, JoinKind.LeftOuter)
Then add a column:
NewColumn = Table.AddColumn(NewStep, "Final Column", each if [NameOfColumnOneOfReplacementTable] = null then [Description] else [NameOfColumnTwoReplacementTable], type text)
---Nate