Bulk Find/Replace in Power Query Using List.Transform() and List.ReplaceMatchingItems()

#powerquery #power_query
Steps:
1.Create/import two tables:
•T1 for the Data to be modified
•T2 for the Find/Replace list
2.Use Table.ToRows() function to make a list of lists for each Find/Replace pair (F_R)
3.Convert Table 1 into a list using fuctions
•Table.DemoteHeaders() to put header names in list
•Table.ToColumns() to put columns into one list of lists
4.Tranform List by adding the following formulas:
•List.Transform() to transform list
•List.ReplaceMatchingItems() with each _ for bulk find/replace
5.Convert list back to table using the following formulas:
•Table.FromColumns() to revert list of list back to columns
•Table.PromoteHeaders() to recover column names

Advanced Editor Code:
let
   Source = null,
   T1 = #table({"Col1", "Col2", "Col3"},{{"A", "B", "C"}, {"D", "E", "F"}, {"G", "H", "I"}}),
   T2 = #table({"Find", "Replace"},{{"A", "Apple"}, {"B", "Banana"}, {"C", "Carrot"}, {"D", "Donut"}, {"E", "Éclair"}, {"F", "Fudge"}, {"G", "Grape"}, {"H", "Ham"}, {"I", "Ice Cream"}}),
   F_R = Table.ToRows(T2),
   Custom2 = Table.PromoteHeaders(Table.FromColumns(List.Transform(Table.ToColumns(Table.DemoteHeaders(T1)), each List.ReplaceMatchingItems(_, F_R))))
in
   Custom2